At times, we need to sum a range of cells in which we add values following some conditions. For a blank cell, you may use SUMIF with blank using “ “ as a criterion. However, using the SUMIF is suitable only when the cells are filled with some value as the criteria you may use operator < > that shows not equal to blank. In summing up the cells, this operator works as the criteria for the functioning when the criteria range is not blank.
Sometimes, we may have to work with a dataset containing blank cells. However, we can assign a resultant value to it. In this scenario, you may have to rule the cells out that contain blank inputs and work with the sum total. In that case, you may use Excel SUMIF Not Blank.
Let’s have a look at some examples to clarify the concept of SUMIF Not Blank:
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 Passengers:
By City and by Month, for both, the Criteria Range conditions change fully. We add the syntax for criteria here.
- “<>” – > It shows the “NOT EQUAL TO” sign and it must be in the double quotation marks because the formula reads it as characters. Using this will let you sum up all the values that are not blank and you can ignore the blank cells entirely while summation process.
- “- > It shows Blanks. Using this double inverted comma without any character lets you sum up all the values having a blank. It also lets you ignore all the cells having some characters or values in them.
For more understanding, you can have a look at the following table.
Case # 2: Using Function Argument
Let’s have a look at an example:
- Pick Cell F2, where you need to apply SUMIF () value as shown in the image below:
- Click on the “Formulas” tab and choose “Math & Trig”.
- A drop-down menu will appear with several computational functions in Excel. Now, you have to scroll down and click on “SUMIF”, which is highlighted in Red.
- Next, you will see a dialogue box appears and you have to add the resulting respective cell reference or values:
Here you have to enter cell reference of City columns in Range input, City name or cell reference in Criteria, and the number of passengers in Sum_range.
- For this, you have to choose the “Range” section given in the dialogue box and click on the blank space. Now, select range starting from A2 and pull down to A17. For instance, select all the row range in City column B and go to the “Criteria” range.
- Now is the time to choose the City cell reference, cell E2 and in the end go to the “Sum_range” section on Column C from C2 to C17.
- Click on “OK” and you will see the value given in Cell E2.
Things to Remember for SUMIF Not Blank
You can use SUMIF Not Blank restrictedly unless the set of data has empty rows given in the criteria range.
Processing Spaces as Blank Cells with Helper Column
While you are in the process with empty cells in Excel, you have to be extra careful. You may see cells as blank or empty, however, Excel will not treat them as blank. It happens when the cells in the dataset have linebreaks, spaces, or anything else including unseen characters.
Well, this counts as a general issue while importing data into Excel from other sources. In case, if you have to process any cells containing spaces the way as if they were empty, the formula mentioned in the earlier case will never work.
Have a look at how the SUMIFs formula does not reflect on cell B9 below (“ “) to be blank:
=SUMIFS (D3:D9,B3 : B9,”<>”)
In case, if you want to treat a cell having spaces as if it was an empty cell, you can simply insert a helper column with the LEN and TRIM functions.
Using the TRIM function, you can cut off the extra spaces right from the starting point to the endpoint of each cell. On the other hand, using the LEN function lets you count the number of residual characters. When the outcome of the LEN function is 0, the Player name would be blank or spaces:
=LEN(TRIM(B3))
To the helper column, you can simply apply the SUMIFs function and it calculates the sum exactly.
=SUMIFs(E3:E9,D3:D9,”>0”)
Image treating space 3
Remember that the helper column is not tricky at all to make and read. That’s why SUMIF Not Blank is helpful in such conditions.
