Start the formula by typing =COUNTIF (. By contrast, a standard. In this example, we are comparing the total sales for orders with empty notes vs. In this example, the function sums units sold, if the value in the Clothing Item column equals Pants. SUMIF can calculate sums based on cells that are blank or not blank. This results in a BLANK, but in the following. Sub TestSumIf () Range ("D10") = Application. If the answer is true, this means cell B1 is blank. If there are no zero values on the sheet that you want explicitly displaying, then there is a per-worksheet option to hid zero values. Sorted by: 0. Both approaches are explained below. =IF (CONCATENATE (A3:C3)="","",SUM (A3:C3)) It simply concatenates the range and checks if the result is empty. Excel SUMPRODUCT with blank cells. I want the weekend total hours to be blank if no work was done on either weekend day. SUMIF Not blank. SUMIFS not working with blanks. Excel SUMIF: If text is empty, not empty (blank, not blank) As you can see in the following screenshot, some orders have notes while others. C16 when the Lead in D5:D16 is not blank (i. For the quick answer see Mr_Nitrogen's answer. 1 Answer. Example 2: Calculate Sum & Ignore Blanks. Sum cells until adjacent cell is not blank. Sometimes you need to check if a cell is blank, generally because you might not want a formula to display a result without input. The syntax for the function is . If the value in B in the next row is a null string then it will do the SUMIF, if not it returns a null string. To calculate an average when corresponding cells are not blank, you can use the AVERAGEIFS function. Conditional Formatting. </tbody>. SUMIF(cell_range, criteria, sum_range) where the first two arguments are required. I am trying to use a SUMIF statement on a large collection of data. First, type ‘=SUMIF (‘ to start the SUMIF function. To sum numbers in one column depending on whether a cell in another column is empty or not empty, use one of the following criteria: "=" to sum cells that are completely blank, i. If you really want to use SUMIF as way to count the empty cells, use something like this: =COUNT(E3:E36)=SUMIF(E3:E36, "1") - but obviously, using COUNTIF is a better approach. =SUMIF(B2:B560,"<=10000",A2:A560) My target is to have the sum of cells in column A if their corresponding value in column B is less than or equal 10,000. Result. The below formula works, however, if there is not data for a month then I would like it to return a blank, NOT a 0 as it is currently. To sum numbers when cells are not equal to a specific value, you can use the SUMIF or SUMIFS functions. e. This formula will return what you want: =IF (SUM (J6:J9), SUM (J6:J9), “”) Note that a 0 result of the logical_test argument of the IF function is equivalent to FALSE. Upvote 0. 0. Cells in each range must be numbers or names,. =SUMIF (range, "<>", sum_range) ARGUMENTS. Sum above cells ignoring blanks. In the example below, the formulas in F5 and F6 both ignore cells in column C that do not contain a value:. . To undo, select Thread Tools-> Mark thread as Unsolved. If you are satisfied with the solution (s) provided, please mark your thread as Solved. To sum values between a given start and end date, you can use the SUMIFS function. Yeah, I thought of that, but blank isn't the same as empty string! So if A1 and B1 contain e. After that, in the [sum_range] argument, refer to the range B2:B13. This needs to be performed on a row by row basis so each entry has their appropriate numbers. For column B, the first data cell for the formula will be B2), then using the fill handle, copy the formula to your required range. We need to find the Qty of the Product not delivered yet and apply the formula in cell D13. Method-8: Highlighting Blank Cells with Excel Conditional Formatting. I can't manually replace blank cells because it is a long sheet. =IF (NOT (ISBLANK ( [Units Sold]2)), "Gained revenue","No Sale") Description. In the case of an empty string, the ISBLANK function returns a “false” value. Formula. The function returns the sum of the. Method #2 – Using COUNTA Function. A good way to solve this problem is to use the SUMIFS function . I want to sum the values of column B where column A has a blank field. Blank. For example, to sum the amounts for which the delivery date is set (a cell in column C is not empty), use this formula:Then you can see all values where the adjacent cells are blank are summed and displayed in the specified cell. “<>” – Criteria – This is symbol of non-empty cells. After 2007, the SUMIFS function was created which allows a. This condition is important, because some cells are empty, some have random strings in it, some are marked with "N. PRODUCT: Returns the result of multiplying a. Note that we could also use the following formula to only sum the points values where the player name is blank:To make your own similar Google Sheet COUNTIF not blank formula, here’s what you’d do: Click an empty cell. Criteria_range1 (required). SERIESSUM: Given parameters x, n, m, and a, returns the power series sum a 1 x n + a 2 x (n+m) +. In the example shown, the formula in cell I5 is: =SUM(SUMIFS(data[Total],data[Color],{"red","blue"})) Where data is an Excel Table in the range B5:F16. Each condition is provided with a separate range and criteria. Press the return key. Returns the string Gained revenue if row 2 of the Units Sold column contains a value, and No sale if the field is blank. Each of the columns has a formula in them to calculate a number. Next, enter criteria for non-blank cells by using “<>”. If not, it will test if the second cell is blank, print a blank space if so, and continue testing if not. If Cell is blank add SUM of another. You can apply the same logic and the not blank operator (<>) to sum cells too, like so: =SUMIFS(A3:A33,B3:B33,"Paid",C3:C33,"<>") Related Reading: Google Sheets DSUM Function. This includes numbers, dates, times, errors, and formulas that return non-text results. RJB78 Board Regular. Because the first argument is blank, evaluation continues with the next argument until a non- blank value and non-empty string is found. Wrong Use of Comparison Operator in SUMIF Function. Method-7: Using IF Function and LEN Function to Return a Value If Cell is Blank. Our example ignores Players A, B, C, and D and sums the scores for unknown Players. The SUMIF function syntax has the following arguments: range Required. Add numbers in a column if the criteria is equal to (or all except for) a specific text, number, or cell. In the example shown, cell G5 contains this formula: = SUMIF (D5:D16,"<" & F5) With $1,000 in cell F5, this formula returns $3,875, the sum of values in D5:D16 less than $1,000. not empty). The above method helps you sum values where the adjacent cell is blank in a certain range. To define criteria, SUMIFS supports various logical operators (>,<,<>,=) and wildcards (*,?,~). In the example shown, the formula in cell H9 is: =LET(x,XLOOKUP(G9,B5:B16,D5:D16),IF(x="","",x)) Because the lookup result in cell D9 is empty, the final result is an empty string (""). This will of course return an . Sum cell values below until blank cell. Syntax: SUMIF(test_range; condition; sum_range) This function identifies those cells in the range test_range that meet the condition, and sums the corresponding cells in the range sum_range. I dislike ISBLANK because it will not see cells that only have "" as blanks. Excel Sumifs alternative. Report abuse. "" to sum empty cells including zero length strings. To create a conditional sum with the SUMPRODUCT function you can use the IF function or use Boolean logic. On the menu, click Insert > Module. The original SUMIF function was limited to just one criterion. How to SUMIF on a dynamic range that includes blanks? 0. e. Type a comma (,) Type “ <> ”. 00. It is used to scan through a range of cells checking for a specific criterion, and then adding up values in a range that correspond to those values. display nothing) based on a conditional test, you can use the IF function with an empty string (""). F5). 4 3 4 BLANK 1 2 5 7 1 BLANK 2 1 4 BLANK 3 In this case the cell called "BLANK 1" would be the sum of the 3 previous rows: 4+3+4=11 "Blank 2" would be 15 and so forth. the SUM range will be coming from the CB sheet, matching column K and summing column L. Use a conditional function to provide a TRUE or FALSE result to highlight or filter out data based on specific criteria. I tried this formula: =SUMIFS (CHILDREN (Amount25)), (CHILDREN ( [Date Paid]25), NOT BLANK), but it did not work. For example, If cell A1 is blank (empty), the ISBLANK function will return TRUE: =ISBLANK(A1) // TRUE if A1 is. Click Insert > Module, and paste the following code in the Module Window. 0. The Excel ISNONTEXT function returns TRUE when a cell contains any value except text. Here is the formula I currently have: =IF(ISBLANK(BM2:BQ2),"","Major Milestone Due") It is returning everything as Major Milestone Due, even if there are certain row ranges with all blank cells. Here is my formula for the moment: =SUM(L6;+K7;-J7) the same formula will be on 90 cells on the same column. Excel sumif only when all are non blank cells. Sum the Transaction Total if the price per unit is greater than 20. We will input the formula into Cell D5; =SUMIF(A4:A9. In the example shown, the formula in cell E5 is: =IF(D5"","Done","") As the formula is copied down it returns "Done" when a cell in column D is not blank and an empty string ("") if the cell is blank. It can be expressed as a number, text, logical expression, or cell reference. In the example shown, the formula in cell G5 is: =SUMIFS(C5:C16,B5:B16,">="&E5,B5:B16,". }, sum_range)) Please remember that this formula works based on OR logic - a cell is. This formula will return what you want: =IF (SUM (J6:J9), SUM (J6:J9), “”) Note that a 0 result of the logical_test argument of the IF function is equivalent to FALSE. After that, in the first argument, refer to the range with the names. To see if cell B1 is blank, write the formula = B1 = “”. If. For <>, if the value is not empty it matches any cell content except the value, including empty cells. Meaning your criteria are E2 and 0. 1. =SUMIF (Type:Type, "Risk", [Likelihood@row:Proximity@row]) although this formula doesn't work but, you get the idea, I'm sure. Modified 6 years, 1 month ago. To test for non-empty cells use <>"" inside the AND function like this: = IF ( AND (C5 <> "",C6 <> "",C7 <> ""), SUM (C5:C7),"") In this formula, notice the SUM function has been moved to the value_if_true argument, and will only run if all 3 cells are not empty. 1. Note that the sum_range is entered last. This particular formula checks if cell A1 is empty. Consider the following example:Then you can see all values where the adjacent cells are blank are summed and displayed in the specified cell. In the below example, COUNTA(A1:A10) returns five as there are five values present. Using column B allows us to avoid having to figure out where in column A to put a SUM () formula. Add numbers in a column if the criteria is equal to (or all except for) a specific text, number, or cell. “=” to sum cells that are completely. We can use the MAP lambda helper function for the count if not blank in Google Sheets. The formula worked in Mac Excel but Apple Numbers . Formula 2: Sum If Not Blank (Multiple Columns) Key Takeaways SUMIF not blank in Excel is a formula used to find the total based on the criteria. Let’s follow the instructions below to learn. Shane Devenshire. Cells in each range must be numbers or names, arrays, or references that contain numbers. To ignore a formula or don't calculate if the specific cell is blank in Excel, we need to check the specific cell is blank or not with IF function, if it's not blank, we can go ahead to calculate with original formula. Formula should be able to sum until blank column in first row or until 1 in helper row. =SUMIF (Type:Type, "Risk", [Likelihood@row:Proximity@row]) although this formula doesn't work but, you get the idea, I'm sure. COUNTA treats a zero-length string as a non-empty cell because it actually contains a formula. Here are the formulas we use in B3 and C3: Finish dates are empty (B3): =SUMIF(D6:D15,"",G6:G15)SUMIFS not working with blanks. Just click Enter and the number of the cells that have values in it will. SERIESSUM: Given parameters , , m, and , returns the power series sum a 1 x n + a 2(n+m) +. 1. I am also assuming that in column C, the two possible values are the logical value FALSE (not the text string "FALSE"), or blank (not a string of 1 or more spaces). I'm playing around with the sumif and sumifs function but I can't seem to figure it out. If the value after the <> comparator is not empty, then Criterion matches any cell content except that value. The range of cells that you want evaluated by criteria. Sum If - By Month: Sum If - Cells Not Equal To: Sum If - Not Blank: Sum if - Between Values: Sum If - Week Number: Sum Text: Sum if - By Category or Group: Sum if - Cell Contains Specific Text (Wildcards) Sum if - Date Rnage: Sum if - Dates Equal: Sum if - Day of Week: Sum if - Greater Than: Sum if - Less Than: Average: yes: Average. You can now use this function in your spreadsheet like this: =sum_until_blank ("B2"). How to perform a sumif using blank cells as a reference? 0. It includes double quotation marks with Excel operators. To sum numeric values that are between two numbers, you can use the SUMIFS function. We can create the following macro to do so:Ignoring empty cells. Here we have two option, First find out the total of cells which are not blank and then deduct the same from total sum. I have tried the formula. Criteria implies to all those cells that contain at least one character length, means not empty. Excel formula: if cell is not blank, then sum. This criteria finds the smallest value in the range and compares that value to each value in the first argument. Count / Sum If - Cell Color: INDIRECT Sum: Sum If - Across Multiple Sheets: Sum If - By Month: Sum If - Cells Not Equal To: Sum If - Not Blank: Sum if - Between Values: Sum If - Week Number: Sum Text: Sum if - By Category or Group: Sum if - Cell Contains Specific Text (Wildcards) Sum if - Date Rnage: Sum if - Dates Equal: Sum if -. Here are two ways to test for blank cells. The ISNONTEXT function takes one argument, value, which can be a cell reference, a formula, or a hardcoded value. Note: With an Excel Table, the formula will automatically update if data is added. The actual cells that are added are determined by using. IF function performs a logic_test if the test is true, it returns an empty string else returns the same value. Thus, the result is 0 when the sum is completed. Using Wildcards with SUMIFS Excel SUMIF: date is empty, not empty. Assign the formula =SUMIFS(C2:C16,A2:A16,F4,B2:B16,"") to cell F6. This means you can use COUNTA as a simple way to count cells that are not blank. We can use the SUMIFS Function to sum all Scores for Players without names in the example below. In the end, enter the closing parentheses and hit enter to get the result. Any assistance would be greatly appreciated. How To Ignore Blank Cells for a Formula. 0. Format Cells dialog box will appear, go to custom tab. Sum if not blank: "<>" to add up cells that contain any value, including zero length strings. The string "<>" matches non-empty cells. Or you can use this way:For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. sum the cells of each row which are formulas. Use the SUMIF Function in Google Sheets . The second argument, criteria, is the criteria to apply, along with any logical operators. I'm trying to add (sum) values with some conditions over an arbitrary non-contiguous cell range in a spreadsheet. “<>” is also known as the empty string syntax. Maybe not, but I had fun working on this SUMIFS formula with empty criteria cells! SUMIFS Formula With Empty Criteria. Result. 3 Answers. In the example shown, the formula in H5 is: =SUMPRODUCT(IF(C5:C16="red",1,0),D5:D16,E5:E16) The result is $750, the total value of items with a color of "Red" in the data as shown. I need to sum the column anyways, so if there are values, then the answer would be the sum of whatever has been entered (whether that's 4 rows or 20. To make XLOOKUP display a blank cell when a lookup result is blank, you can use a formula based on LET, XLOOKUP, and the IF function. So if you want to calculate if all cells are non blank then use below formula. Note that "=0" does not match empty cells. Sum of values in Column A is done, a condition that the corresponding. I have the same formula just up by a month for the entire year without. I still want the column to show a running total. So, let’s see how to remove them. For example, here, I want to sum all cells which adjacent cells are not equal to the text “Apple”, the following formula can help you. =SUMIF (C2:C7,">25000") This formula adds the numbers in the cell range C2 through C7 only if they are greater than 25,000. Current formul. then your "blank" cells are not really blank, and that is why your formula is not working the way you want. Goal: For a summation terminating in a single cell, I'd like to sum the range I38:I39 depending on whether or not the adjacent/corresponding cell in H38:H39 has any value other than a formula. For that, go to any cell where you want to see the output and click on fx (a tab to insert functions in Excel), as shown below. Sumif adjacent cell containing text in Excel. 0. Case # 1: SUMIF Not Blank In the following dataset, you will find some blank cells in range criteria in which suppose that we need the Total number of. A8 - 24. but grouped by the column WholeSale and IGNORE the rows without text in this column. The following code does not return any values for the SUMIF functions, no errors to report. This is a not-equal operator that tells Excel to count. 1 Answer. The easiest way to sum multiple columns based on multiple criteria is the SUMPRODUCT formula: SUMPRODUCT ( ( sum_range) * ( criteria_range1 = criteria1) * ( criteria_range2 = criteria2 )) As you can see, it's very similar to the SUM formula, but does not require any extra manipulations with arrays. For the most part, it works just fine. This includes numbers, dates, times, errors, and formulas that do not return text. Here's how it works. If E7 (see . If they didn't do the work, then the column would remain blank. Here this formula uses SUMPRODUCT function. To sum certain cells when other cells are not blank, use the SUMIF function, which is especially designed for conditional sum. Discussion Options. Returns the string Blank if row 1 of the Units Sold column contains no value. You can apply the same logic and the not blank operator (<>) to sum cells too, like so: =SUMIFS(A3:A33,B3:B33,"Paid",C3:C33,"<>") Related Reading: Google Sheets DSUM. The result is $205, the sum of Total where the Color is "Red". If the bank account values are in column A and the cash values are in column B from rows 1 thru 121, then the sum of cash expenses is =SUM (B1:B121) and the sum of bank account expenses is =SUM (A1:A121) The sum of all expenses is =SUM (A1:B121) GSNU2020. Alternatively, you can open the VBE by clicking the Visual Basic button of the Developer tab. In this example, the goal is to count cells that do not contain a specific substring. First, in the cell, enter the =SUMIF (. If omitted, the range is used for summation. It appears that you want the result to appear as an EMPTY TEXT (“”), if the sum range solely consists of blank cells. If a helper column is not appropriate for your needs, then you can use the SUMPRODUCT Function in combination with the LEN and TRIM Functions to sum. Blank cell as criteria for SUMIFS. The selected range may contain dates in standard Excel format (examples below). FORMULA. I currently have =SUMIF (H2:H1768,"<>",S2:S1768) but I can tell it's not returning correctly. Write this formula in cell B4 and fill right (CTRL+R). criteria: This is the condition that determines which cells to include in the sum. Let's break down this formula: The range: The range is the group of cells the criteria is being tested against. "" to sum blank cells including those that contain zero length strings. 0. Now it's seen as a blank string and not an empty cell so Excel won't treat it like a zero. The formula in cell D5 looks like this: =FILTER(B5:B14,B5:B14>100) Notice that the include argument is a logical expression, B5:B14. = SUMIF (B:B,"<>",A:A) Explanation: The function checks the cells which are not blank and excel if the cell is not blank, it records the value of the corresponding cell. For example if you want to do the same thing as above, we can use the SUMPRODUCT function to do so: We want to sum range D2:D20 if date is equal to F3. The SUMIF function is one of the older functions used in spreadsheets. It allows you to add numbers in a specific range based on a given condition, such as values that meet a specified condition, fall within a certain range, or match a particular text string. Row + 1 If Cells (i. + a i x (n+ (i-1)m), where i is the number of entries in range `a`. The range of cells to sum. Problem is that for these empty cells, the above formulae skip the empty cells and return value for the next cell that is not empty, breaking their correct. g. ## 0. Result. Sum if not blank: "<>" to add up cells that contain any value, including zero length strings. In the example below, SUMIF is used to sum the amounts in column C depending on whether column D contains "x" or is empty:. Blank cell as criteria for SUMIFS. The SUMIFS function sums the cells in a range that meet multiple conditions, referred to as criteria. Select the blank cell you place the counting results, and click Kutools > Kutools Functions > Statistical & Math > COUNTBYCELLCOLOR. Spill Range is Too Big . To average a range of cells if a cell in another range is not blank, utilize "<>" for criteria. 1. We have to use OR so we mean: If any member of the set (D2 or C2) is blank, return an empty cell. You need to be careful when interacting with blank cells in Excel. contain absolutely nothing. =IF(1=0,1,"") , it is considered not empty, even though the result is essentially nothing. Select A1:C {last blue row number}, remove filter (if grouping is required), subtotal with At each change in: ‘ (Column A)’, Use function: ‘Sum’, Add subtotal to: tick ‘Sum’, OK. To sum if greater than value we can use SUMIF function. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. #4: If cell contains text. The range of cells that you want evaluated by criteria. . Note that we could also use the following formula to only sum the points values where the player name is blank: Table of Contents hide. has content), you can use a formula based on the IF function. To test if all cells in a range are empty, you can use a formula based on the COUNTA or SUMPRODUCT functions. Count not blank cells - COUNTIF function. like say invoice A have two figure 1 and 2 so the total show 3. =SUMIFS(C3:C8,B3:B8,"") We use double quotes (“”) to represent a blank cell in Excel. We only need to use comparison operator “Not equal to” (<>) in the criteria argument and the SUMIF function sums up all the cells in the sum_range argument that are not empty or blank. Here,. Next. SUMIFS has some other quirks, which are detailed in this article. I have 10 columns where some of the columns may or may not contain data. The formula of SUMIF is =SUMIF (range,criteria, [sum_range]) where all 3 arguments are mandatory. . Next, press ALT + F11 to open the VB Editor. In that case, we will use IFERROR with SUM to have the result. The criteria: Our criteria is a non-blank status indicated by "<>" - a "less than" and "greater than" sign wrapped in double quotes. For multiple conditions (e. sum_range (optional): This is the range of cells containing the values you want to sum. I want to create a formula in B8 that calculates the sum of the amounts if the description of that amount contains "**" (or some other denoting text). However,. To find the total sales for Pina Colada: Go to cell F6. If yes then sum all 'available' by product. Lastly, we will press the Enter key to return the result. To sum certain cells when other cells are not blank, use the SUMIF function, which is especially designed for conditional sum. E5:E14 → is the range where the SUMIF function will search for blank cells. Otherwise, “Does Not Exist” is returned. Excel SUMIF, sum another cell if empty. jht wrote: > How to sum cells and ignore the #div/0! 's ? IMHO, it is best to avoid the #DIV/0 errors in the first place. “” → is the criteria for finding blank cells in the range. See the following section for more details. The range argument is the range of cells where I want to look for the criteria, A2:A19. Sum if blank: "=" to sum cells that are completely blank. The syntax of the SumIf function in Google Sheets is as follows: =SUMIF (range, criteria, [sum_range]) Range: The range of cells that you want to evaluate. I can write the individual formulas to get the red to work and an. You will see a label called “Type”. Maybe not, but I had fun working on this SUMIFS formula with empty criteria cells! SUMIFS Formula With Empty Criteria. but i only get the sum of the rows that are non zero (rows: 1, 3, 6, 7 and 9)Hi Joe4. Example. In the table below, supposing you wish to find the total amount for the items that are already delivered and those that are not yet delivered. The word "blank" is somewhat misleading in. I used SUM formula in the botton cells to get sum of all values from each row , the bottom cells display TOTAL and i will assign the TOTAL c. If they entered values into the column (even if the values are 0 all the way down the column), then they have completed the task. End (xlUp). Method 1: Sum if not blank use the SUMIF function So write the formula where you want to get the sum. Applying “Not Blank” Operator 2. This is the sum of numbers in the range F5:F16 where corresponding cells in C5:C15 are not equal to "Red". 3. e. if_empty - [optional] Value to return when no results are returned. Use of VBA SUMIF to Sum Cells Corresponding to Blank Cells. Example 2. e. “<>” is the criterion that checks for non-blank cells. The next function we will use to count if not blank cells are present in a range is the COUNTA function. In the example shown, the formula in cell F5 is: = AVERAGEIFS ( price, group,"<>") Where price (C5:C16) and group (D5:D16) are named ranges. SUM Excel column if row not blank, otherwise take value from left cell. Not an expert on sheets (so this may somehow identical to marikamitsos's answer, though their solution did not work for me for 0-valued rows) I have done this in Google Sheets recently. We will use a <> operator (i. Method-5: Applying the IF and COUNTIF Functions to Return Value If Cell is Blank. The sum of 2 cell if a cell is not blank. The current Range is B2:B30, it gives me the number 29 even though the cells are blank. When applied to a cell range containing text strings, the SUM function returns, and so does the COUNT function. Check if there any value in column sold greater than zero. I'm looking for a way to make sum only if one of both cells are not blank otherwise leave the sum cell blank. Syntax COUNTA(Range) Returns the number of cells that have texts and numbers. To sum numbers when cells are not equal to a specific value, you can use the SUMIF or SUMIFS functions. To calculate the sum of the values between two empty cells the result should be only one for each "flight". In a blank cell, enter this formula =SUM (IF (ISERROR (A1:C6),"",A1:C6)), see screenshot: Note: in the above formula, A1:C6. many thanks. HOWEVER, I would like it to SUM only the MIN values in the Sum_range, where both criteria are met. Row 3 meets the criteria. Example Col to Match Col to Sum A B 1 2 23 1 1 3 0 3 So in column C I need my sumif. Use the SUMIF Function in Google Sheets . The SUMIFS Function sums data that meets certain criteria. 2. After that, enter double quotation marks (starting and closing). SUMPRODUCT if not blank. To return a blank result (i. The sum of the points in column B where the player in column A is not blank is 45. Meaning your criteria are E2 and 0. Download the featured file here: this video I demonstrate how to ig. =SUMIF(C2:C47, "<>Steven Spielberg", F2:F47) Sum if based on blank or non-blank cells. To sum values based on blank cells, please apply the SUMIF function, the generic syntax is: =SUMIF (range, “”, sum_range) range: The range of cells that contain blank cells; “”: The double quotes represent a blank cell; sum_range: The range of cells you want to sum from. In this section, you will learn how to sum values where the adjacent cells contain texts in Excel.