If C5 is not empty, ISBLANK returns FALSE and the IF function returns COUNTA function with an expanding reference like this:Team B. the SUM range will be coming from the CB sheet, matching column K and summing column L. You need to be careful when interacting with blank cells in Excel. After that, in the [sum_range]. WorksheetFunction. I have a data issue that has been perplexing me for a few weeks now. The cells are blank, not zeros. First, enter SUMIF in a cell where you want to calculate the sum. =SUMIF (range, "<>", sum_range) ARGUMENTS. Option 1: Don’t display zero values. 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. Report abuse. 1. Sum cell values below until blank cell. Once items in the range are found, their corresponding values in Sum_range are added. Trying to get a count based on cells in two different columns - I want to count all cells in column A that have a value, but I only want these values to be counted if there is no value in a cell in column B Ex. The formula thats inside each cell is an IF statement that analyses some adjacent columns, returning a priority value when two columns are populated: Code: =IF (AH2="",AF2,AH2) Thanks. SUMIFS (not blank, and othe conditions) 0. SUMIF can calculate sums based on cells that are blank or not blank. First of all open your worksheet where you need to add the cells based on background colors. 40. Result. Steps: Select a cell where you want to have the summation (i. Returns the string Gained revenue if row 2 of the Units Sold column contains a value, and No sale if the field is blank. If anyone could help! Sincerely!Plus you can add values in a different cell range based on the criteria in another. You could differentiate if you want to hide all zeroes from the entire worksheet or just from selected cells. The generic syntax for SUMIF looks like this: = SUMIF ( range, criteria,[ sum_range]) The SUMIF function takes three arguments. It seems so simple, yet I have had nothing but errors. Here are the steps to use Sumif in a text and number column in Google Sheets. Cells in each range must be numbers or names, arrays, or references that contain numbers. In this section, you will learn how to sum values where the adjacent cells contain texts in Excel. The goal is to sum only time greater than 30 minutes, the "surplus" or "extra" time. SUMPRODUCT (ISNUMBER ( range )*1)>0. but grouped by the column WholeSale and IGNORE the rows without text in this column. Formula gods, looking for help, please. QUOTIENT: Returns one number divided by another, without the remainder. From our dataset, we can easily do that. Method #2 – Using COUNTA Function. 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. Not how B2 is not empty but contains a formula. 1. In this example, we will use IF and OR function together. This works in Google Docs and MS Office, but in LibreOffice and OpenOffice it only summarizes when the call has a value. This will of course return an . Explanation: ISBLANK : function checks the cell D5. Using Helper Column with LEN and TRIM FunctionsYou can use the following formulas in Excel to sum a range of cells if the value in a corresponding range is not blank: Formula 1: Sum If Not Blank (One. "" to sum blank cells including those that contain zero length strings. Since there are nine cells in the range. After applying the sum formula, select all the cells which contain the sum formula and press ctrl+ 1. The result is an array of values that automatically spills into a range of cells, starting from the cell where you enter a formula. If the team name is not empty in column A, then “Team Exists” is returned. In the tab "Invoice WholeSale", cell "B3" I need a query to SUM the tabs: "Stock Warehouse Moves". The selected range may contain dates in standard Excel format (examples below). I need to sum them only if a value in entered in a separate column (S2:S1768). A4 - BLANK. Sum if not blank. 1. To sum if cells contain a specific text, you need to use a wildcard when specifying the criteria in the SUMIF function. If C1 is text, such as a space, it will remain blank. (Note: I don't want to. The function returns the sum of the recorded values. In the following example, IFNA tests the result of the VLOOKUP function. 2 - A Blank cell can mean an empty cell or a cell with a null string. = 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. Next, refer to the Donation column from where you need to sum the values. Enter this formula into a blank. The last argument, sum_range, is the range that should. And tada! For multiple conditions (e. You can use the sum_range argument for adding cells in a range other than the. Similarly, we have to. To sum numbers when cells are not equal to a specific value, you can use the SUMIF or SUMIFS functions. g. The actual cells that are added are determined by using. If you really don't want a 0 when none of the cells have a value, use: =IF (COUNTA (C4,E4,G4)=0,"",SUM (C4,E4,G4)) Share. Excel Formula - Sum in some Column If Cell in The Same Row in Different Column is NOT Blank. Use the formula: =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. The selected range may contain dates in standard Excel format (examples below). The IF function first checks if cell C5 has a value with the ISBLANK function: ISBLANK(C5) // TRUE if empty, FALSE if not If C5 is empty, ISBLANK returns TRUE and the IF function returns an empty string ("") as the result. 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. Although COUNTIF is not case-sensitive, the SUMPRODUCT version of the formula can be adapted to perform a case-sensitive. Result. Method-6: Using IF Function and SUMPRODUCT Function to Return a Value If Cell is Blank. You can use the following formula to calculate the average value of a range in Google Sheets only if every cell in the range is not blank: =IF(COUNT(A1:A10),AVERAGE(A1:A10),0) This formula calculates the average value of cells in the range A1:A10. Notice again that SUMIF is not case-sensitive. Calculate If Not Blank using IF function with OR Function. Sum cells until adjacent cell is not blank. e. For the logical test, we are using the COUNTIF function like this:. Excel formula: if cell is not blank, then sum. Don't format those cells that you want. Applying “Not Blank” Operator 2. Output: Summing cells using SUMIF based on background colorTo get a count of values between two values, we need to use multiple criteria in the COUNTIF function. In the example below, the positive values show the cell color, negative values are red and the zero values are blank. Sum if Not Blank. Sample data to determine if a cell is not blank. First sort the blanks to the bottom either with filters or another formula. 50), Shaving Cream (11. For example, we could use the following formula to return the value of the points column divided by two if the cell in column A is not empty. “=” to sum cells that are completely blank SUMIF function has many parameter: SUMIF (range, criteria, [sum_range]) here, the criteria is applied the same values that are being summed. 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 -. Now it's seen as a blank string and not an empty cell so Excel won't treat it like a zero. Add numbers in a column if the criteria is equal to (or all except for) a specific text, number, or cell. The formula would be. In the example shown, the formula in cell F5 is: = AVERAGEIFS ( price, group,"<>") Where price (C5:C16) and group (D5:D16) are named ranges. If the criteria is a reference to an empty cell, the COUNTIFS function treats it as a zero value (0). Sum the Transaction Total if the price per unit is greater than 20. 1. The word "blank" is somewhat misleading in. In the example shown, the formula in cell G5 is: =SUMIFS(C5:C16,D5:D16,"") The result is 61,600, the sum of amounts in C5:C16 when corresponding cells in D5:D16 are not blank. Select a blank cell, C3 for instance, and type this formula =SUMIF (A1:A14,"<>#N/A"), press Enter. 0. Each condition is provided with a separate range and criteria. Enter the following formula, replacing the cell references and criteria with your own. SUMIF function has many parameter: SUMIF (range, criteria, [sum_range]) here, the criteria is applied the same values that are being summed. You can format each individual cell. The reason I found using the SUMPRODCT is that, it stick with the criteria in G2, H2 & I2, if doesn't matches with their respective data then returns 0, but SUMIFS fails to test criteria. Goal: - Sum set range into new reference cell. If omitted, the range is used for summation. The SUMIF Function: an Overview. Now, refer to the Name column where you have blank cells. Perform this for 8 different ranges spanning 42 columns. sumif doesn't run "not" when cell is blank. Count the number of rows where the “In Stock?” checkbox is checked. This should display the sum of positive numbers in cell C2. SUMIFS: Sum if not blank: SUMIFS SUMPRODUCT FILTER SUM: Sum if one of many things: SUMIFS SUMPRODUCT ISNUMBER MATCH FILTER: Sum if x or y: SUMIF SUMPRODUCT ISNUMBER MATCH:. If you are using a cell reference then the formula in E4, shown in E5, works or if you decide to incorporate the criteria into the formula than the one in E7, shown in E8, works. SUMIF. 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. Copying the formula in cell G3 into cell G4 returns the value $ 7,500, which is the sum of orders with green background color. 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. At the end, it prints the SUM if all are blank. To calculate an average when corresponding cells are not blank, you can use the AVERAGEIFS function. Note the formula in F2 is shown in G2 and is simply ="". For cell A4, the formula returns. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. Here we have two option, First find out the total of cells which are not blank and then deduct the same from total sum. See screenshot: 2. Someone emailed to ask how they could ignore one criterion in a SUMIFS formula, if that cell is empty. = 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. Using SUMIF function you can sum numbers based on Non blank cells criteria. 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. =SUMIF (A1:A6,"<20”) You can also keep the criteria value, i. To sum values between a given start and end date, you can use the SUMIFS function. Click the AutoSum command on the Ribbon's. This results in a BLANK, but in the following. SUMPRODUCT if not blank. For example, if you use a formula like SUBTOTAL (109, A1:E1) to sum numbers in a horizontal range, hiding a column won't affect the subtotal. So I only want to apply the SUMPRODUCT and SUM to those rows where all columns have an entry: In the below example, I only want sumproduct of rows 1, 2 and 4 and sum of A1,. One of those conditions is if the cell is blank, then I want the value of another cell considered for the sum, if it is not blank, then I want it excluded. =LEFT ( [COMMODITY CODE]@row, FIND ("*", [COMMODITY CODE]@row) - 1)Suppose you want to sum orders’ amounts for either of the products “Orange” and “Apple” supplied as criteria in array constant then you need to provide multiple criteria in SUMIFS function as follows; =SUM (SUMIFS. After that, in the first argument, refer to the range with the names. Let's break down this formula: The range: The range is the group of cells the criteria is being tested against. =SUMIFS(C3:C8,B3:B8,"<>") Treating Spaces as Blank Cells – With Helper Column. =SUMIFS(C3:C9,B3:B9,"<>"&E3) Start with the logical operator within double quotes (“<>”) and use the & symbol to join the operator with the cell. =INDEX (D:D, AGGREGATE (15, 6, ROW ($1:$9999)/ ( (A$1:A$9999="Jan")*SIGN (LEN (D$1:D$9999))), 1)) However, if 2003 is in a cell and you have to use it to determine which column to retrieve, it gets a little more. SUM Excel column if row not blank, otherwise take value from left cell. It appears that you want the result to appear as an EMPTY TEXT (“”), if the sum range solely consists of blank cells. Returns the string Blank if row 1 of the Units Sold column contains no value. The above method helps you sum values where the adjacent cell is blank in a certain range. =SUMIF(C2:C47, "<>Steven Spielberg", F2:F47) Sum if based on blank or non-blank cells. 1. For the quick answer see Mr_Nitrogen's answer. 0. To fix the error, follow these steps:. Instead I prefer COUNTBLANK, which will count "" as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has "". The following formula is shown in cell E3. 0. 3. I can't manually replace blank cells because it is a long sheet. Formula with SUMIF to SUM Values Cells which are Not Blank. Coalesce ( "", "2" ) Tests the first argument which is an empty string. I'm working w/ Google Sheets. Download the featured file here: this video I demonstrate how to ig. In A1, value is 1 so the output in A3 is 1. Solution: Define Criteria Correctly. Blank and text values are ignored. In the example shown, the formula in F5 is: =SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D5:D16"),E5,INDIRECT("'"&sheets&"'!"&"E5:E16"))). I have tried to get it to work but no success yet. After this, paste the “ColorIndex” UDF in the Editor. 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. 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. "<>" to sum non-blank cells including zero length strings. The first argument, range, is the range of cells to apply criteria to. 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. Conversely, a non-zero result thereof is equivalent. Then off to the side there are four user input boxes that filter the results down even further based on the wildcards. The sum of the points in column B where the player in column A is not blank is 45. You can now use this function in your spreadsheet like this: =sum_until_blank ("B2"). To solve this problem, you can simply use the sum formula along with custom formatting to display a blank space instead of zero. After that, in the first argument, refer to the range A1:A10. criteria: This is the condition that determines which cells to include in the sum. 2. Example. By its nature, the COUNTA function counts the cells in a range that are not. How to Use SUMIF Function to Sum Not Blank Cells in Excel 1. And still, we say that Excel SUMIF can be used to sum values with multiple criteria. In the image above I have numbers in various cells from columns A:E, then I have a formula in cells F1:F3. SUMIFS has some other quirks, which are detailed in this article. Criteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific criteria. I mean in cases Y column has blank value is not depend on X column. 2,303. Any assistance would be greatly appreciated. In the example below, the formulas in F5 and F6 both ignore cells in column C that do not contain a value:. The following example shows how to use this formula in practice. End the formula with a closing parenthesis ) and then press Enter. criterion_range1 — The group of cells to be evaluated by the criterion. sum the cells of each row which are formulas. Excel SUMIF, sum another cell if empty. So, you’ve got a 0 in the denominator again,. In the “ [sum_range. Result. Sum Range Is in Text Format. Cells in each range must be numbers or names, arrays, or references that contain numbers. Formula. To define criteria, MAXIFS supports various logical operators (>,<,<>,=) and wildcards (*,?,~). Criteria implies to all those cells that contain at least one character length, means not empty. The range of cells that you want evaluated by criteria. By contrast, a standard. If somehow "C_Durations" column/row is BLANK then calculate sum of column/row "Outage Durations". For example, to find out if the range A2:A5 contains any numeric value, the formulas would go as follows:The MAXIFS function returns the largest numeric value in cells that meet multiple conditions, referred to as criteria. After applying the sum formula, select all the cells which contain the sum formula and. Sum cell values above until blank cells with VBA code. Unfortunately, there is no formula way to result in a truly blank cell, "" is the best formulas can offer. How to sum if cells are not equal to a certain value in Excel? To sum data that not equal to a certain value in Excel, the SUMIF function can also help. "" to sum empty cells including zero length strings. So, let’s see how to remove them. Meaning your criteria are E2 and 0. 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. 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 first range contains a few empty cells. There’s an abbreviated syntax to check if a cell is blank. Instead, it is more flexible to use a separate cell to define the criteria’s value. “” → is the criteria for finding blank cells in the range. Labels:Also, please keep in mind that both COUNTIF and COUNTBLANK count cells with empty strings that only look blank. 0. We need to find the Qty of the Product not delivered yet and apply the formula in cell D13. Calculate If Not Blank using IF function with OR Function. To do this, we can type in the formula “=SUMIF (D2:D8,”<>”,C2:C8)”. Here are two ways to test for blank cells. 1. not empty. The Net Amount % 1 measure first evaluates the ratio between two blank measures for the Soda product. The formula worked in Mac Excel but Apple Numbers . At the core, this formula uses the IF function set up to return a check mark ( ) when a logical test returns TRUE: =IF(logical_test," ","") If the test returns FALSE, the formula returns an empty string (""). , non-blank cells and discount more than 20%), we can use the COUNTIFS function. #4: If cell contains text. In cell D3, enter the formula: = IF (C3<>""," Not blank "," Blank ") The symbol <> in Excel means “ not equal to ” “” in Excel means empty string, or blank; C3<>”” means C3 is not equal to blank, or C3 is not blank. I want to return a blank if nothing was entered, but to run the below formula if there is an entry. Our example ignores Players A, B, C, and D and sums the scores for unknown Players. I want the weekend total hours to be blank if no work was done on either weekend day. Blank. If so it will print a blank space. LEN(A1) to calculate the characters in each cell. To sum value if cells are not blank you can apply the Excel SUMIF function. If the answer is true, this means cell B1 is blank. Thread starter RJB78; Start date Apr 18, 2017; Tags blank cell empty cell sum sumif R. However,. @Gareth: No. First, type =COUNTIF ( in cell B1 to start entering the function in the cell where you want to get the result. The original SUMIF function was limited to just one criterion. 00. The formula looks like: =SUMIF(D3:D9,">"&G2)I have the following (simple) problem with the sumif formula: Two cell ranges in column A and column B. To use SUMIF to sum values for the blank criteria, you just need to specify a blank value in the criteria argument in the SUMIF function. hi, Apologies for not being clear earlier, i want the sumif to pick a blank if the cell contains a blank and pick zero if cell has a zero value. 1. Wrongly Defined Criteria Argument in SUMIF Function. ISNONTEXT also returns TRUE when a cell is empty. The exact place to find this option varies between versions of Excel so you'll need to tell us which vesrion you are using (or search its help for "zero values" or similar). A good way to solve this problem is with the SUMIFS function. If not blank multiple cells: IF ISBLANK NOT XLOOKUP: If NOT this or that: IF OR NOT: If this AND that: IF AND: If this AND that OR. The range is "G8:G3561". 5 people found this reply helpful. 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 a cell contains even a space, then it will meet criteria and will be considered as Non Blank cell. If cell is blank, return specific text; if cell is not blank return the value in that cell. 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. B. For instance, the following AVERAGEIF formula calculates an average of cells B3 through B15 if a cell in column C in the same row is not blank: =AVERAGEIF(C3:C15, "<>", B3:B15) AVERAGEIF wildcard (partial match)Solution 2: Correcting the Criteria Argument. Re: Sumif a certain word is not there. =SUMIF ( [Clothing Item]1: [Clothing Item]3, "Pants", [Units Sold]1: [Units Sold]3) Description. You can use the below steps: First, in a cell enter the SUMIF function. Right click on the cells that you want to hide the zero in and apply the formatting for the positive value followed by a ; negative values; zero values. Following examples will explain the. But note, this. Make SumIf ignore words? 1. SUMIF (B5:B14,”<>”,C5:C14) Here, B5:B14 range is the criteria range upon which the criteria will be checked. If you know you want the data from the fourth (e. How to perform a sumif using blank cells as a reference? 0. In that case, we will use IFERROR with SUM to have the result. not empty). In the above formula, you have used SUMIFS but if you want to use SUMIF you can insert the below formula in the cell. Summing Up Total Run of Unnamed Players. Because there are 10 cells in the range, the expression returns an array that contains 10 results like this:. 0. PRODUCT: Returns the result of multiplying a. Wrong Use of Comparison Operator in SUMIF Function. Blank. 2,303. Enter this formula: =SUMIF(A2:A20,"<>apple",B2:B20) into a blank cell where you want to output the result, and then press Enter key, you will get the calculate result as you need, see screenshot:. With the NOT Function, you can test the inverse: is a cell not blank? =IF(NOT(ISBLANK(C2)),C2*D2,"") ISBLANK Range – COUNTBLANK. Lastly, we will press the. 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. I'm new at this, and I created this formula =IF(SUM(G16:H16)+I15,SUM(G16:H16)+I15,""), but it still shows the total in the last column. The evaluation range is first and the sum range is last. However, I want to exclude such cells. You can add up numbers in a column based on whether they are blank or not. Applying “Not Blank” Operator 2. I have 3 columns I need to look at to add certain numbers in my 4th column, and the third criterion column must be blank in order to add the number in the 4th cell. The string "<>" matches non-empty cells. I still want the column to show a running total. To sum numbers when corresponding cells are equal to x or y, you can use the SUMIFS function with the SUM function and an array constant. Using SUMIF on a range of columns or SUMPRODUCT to ignore text items. You can add up numbers in a column based on whether they are blank or not. I tried this formula: =SUMIFS (CHILDREN (Amount25)), (CHILDREN ( [Date Paid]25), NOT BLANK), but it did not work. This fx tab is available just below the Menu bar. Here is the MAP alternative to the above COUNTIFS Not Blank formula in Google Sheets. Syntax COUNTA(Range) Returns the number of cells that have texts and numbers. not empty). 📌 Steps:Quotation marks around “South” specify that this text data. We will use a <> operator (i. In this section, you will learn how to sum values where the adjacent cells contain texts in Excel. I currently have =SUMIF (H2:H1768,"<>",S2:S1768) but I can tell it's not returning correctly. Criteria_range1 (required). Just make sure that there are no blank cells in the referenced cell range A2:A6. Excel SUMIF Function Condition with Numerous Comparison Operators & Cell Reference. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. 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. SUMIF looking at a range of text criteria AND returning. Sumifs <> not operating as an AND function. 2. "SUM(A2,B2)". Hello, I need your help - we have 2x columns "Outage Durations" & "C_Durations" we would like to calculate the Sum of column "C_Durations" 1st if the column/row is not Blank. F5). Now it's seen as a blank string and not an empty cell so Excel won't treat it like a zero. Any advice would be greatly appreciated, Thank. Maybe not, but I had fun working on this SUMIFS formula with empty criteria cells! SUMIFS Formula With Empty Criteria. The range that is tested using Criteria1. To sum numbers if values in a criteria range begin with specific text, you can use the SUMIF function or the SUMIFS function. sum if cells not blank. In this example, we are comparing the total sales for orders with empty notes vs. If you select Custom on the Number tab of the Format dialogue box, you should see General in the top box and a list of alternatives that you have used below it. 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 (""). excel; vba; Share. (Click image to enlarge) I need the blue cell in column H to be blank if the formula does not produce a result, but I also need it to display a 0 if the blue cell in column Z has a 0 and this needs to be linked to the job number in column B, which is also in column T. If not blank then sum How to use: Sum if is not blank. We are then subtracting the already counted values above where the formula is placed in column C to get the new sum. How To Ignore Blank Cells for a Formula. The LAMBDA and its helper functions make impossible things possible in Google Sheets. 1. In our example, We can reduce the sales without agent from total sales;Hello there, I searched everywhere and couldn't find what I'm looking for. Progress so far: A more patterned example is summing values in A2, C2, E2, G2 and I2 with the condition that the values are negative. A. =SUMPRODUCT (NOT (ISBLANK (B3:B7))*C3:C7) It adds numbers and returns a total if the corresponding value in B3:B7 is not a blank cell. In Range argument, we have to select the cell range with different criterias. This will write the outcome only if C1 is a number. Sorted by: 0. e. In this example, the function sums units sold, if the value in the Clothing Item column equals Pants. Take note of the following limitations: The cell reference must be quoted.