Excel SUMIF Not Blank
To use SUMIF with blank is very simple we use “ “ as a criteria for a blank cell, but to use SUMIF when only the cells are not blank as the criteria we will use the operator <> which means not equals to blank, this operator acts as the criteria for the function in summing up the cells when the criteria range is not blank.
There are times when we have a dataset that contains blank inputs; however, we have a corresponding value assigned to it. In such cases, we may want to exclude the cells that contain blank inputs and do the sum total. In such cases, use the criteria of SUMIF “Not Blank.” An example could be to know the total number of Passengers and exclude blank names criteria.
Example #1 – SUMIF Not Blank
The below set of data contains certain blanks in range criteria, and suppose we still want to know the Total No. of passengers:
Here now, Criteria Range conditions completely change for both at by City and by Month. Important to note the condition which we put here in the syntax for Criteria
- “<>” -> Indicates NOT EQUAL TO sign, and it has to be out in double inverted commas as the formula processes it as characters. When we use this, the formula sums up all the values which are not blanks and ignores the blank cells completely during summation.
- “-> Indicates Blanks. Double inverted commas with no characters in it, signifies equals to blank. When we use this, it sums up all the values containing blanks and ignores cells that contain some characters/ value in it.
For the ease of understanding, the final output of Table 1 and 2, refer to the below explanation:
Example 2 – Using Function Argument
Lets look at the example.
- Select Cell F2(active cell), where we need SUMIF() value as shown below:
- Click on the “Formulas” tab and select “Math & Trig.”
- A drop-down appears with various mathematical functions in excelMathematical Functions In ExcelMathematical functions in excel refer to the different expressions used to apply various forms of calculation. The seven frequently used mathematical functions in MS excel are SUM, AVERAGE, AVERAGEIF, COUNTA, COUNTIF, MOD, and ROUND.. Scroll down and click on “SUMIF”(Highlighted in Red)
- A dialog box appears as shown below, and the next steps would be to input corresponding respective cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1. or values:
Here we will insert cell reference of City column in Range input, City name/ cell reference in Criteria input, and No. of passengers in Sum_range.
- Go to the “Range” section in the dialog box, click in the blank space and select range starting from A2 and drag up-to A17, i.e., select all the row range in City column B. Then go to the “Criteria” section and select the City cell reference, cell E2 and lastly similarly go to “Sum_range” section in Column C from C2 to C17.
- Click on “OK,” and we shall see the value in Cell E2.
Point to Remember
- SUMIF not blank has limited use unless that dataset contains blank rows in the criteria range.
This has been a guide to SUMIF, Not Blank. Here we discuss how to add cells based on Blank / Not Blank Criteria using SUMIF along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –