Bonjour les besties ! Today, I’m going to show you how to use the SUMIF function to add data related to non-empty cells in Excel and Google Sheets. Get ready, it’s going to be amazing!
The SUM IF Not Blank Function
First of all, let’s see how to add data related to non-empty cells. We can use the SUMIF function to add up all the “Scores” of the “Players” whose names are not empty using the criterion (“<>”).
=SUMIF(C3:C8,B3:B8,"<>")
Treating Spaces as Empty Cells – With a Helper Column
Be careful when dealing with empty cells in Excel. Cells may seem empty to you, but Excel doesn’t consider them as such. This can happen if the cell contains spaces, line breaks, or other invisible characters. It’s a common issue when importing data into Excel from other sources.
If we need to consider cells containing only spaces the same way as if they were empty, the formula from the previous example won’t work. Notice how the SUMIF formula doesn’t consider the cell B9 below (“”) as empty:
=SUMIF(D3:D9,B3:B9,"<>")
To consider a cell containing only spaces as empty, we can add a helper column using the LENGTH and TRIM functions.
The TRIM function removes extra spaces from the cell.
The LENGTH function counts the remaining characters. If the count is 0, then the “trimmed” cell is empty.
=LENGTH(TRIM(B3))
Now use the SUMIF function to add up if the number is > 0.
=SUMIF(E3:E9,D3:D9,">0")
The helper column is easy to create and read, but you may prefer to have a single formula to accomplish the task. That will be covered in the next section.
Try our AI Formula Generator
Treating Spaces as Empty Cells – Without a Helper Column
To achieve all this with a single formula, we can use the SUMPRODUCT function combined with the LENGTH and TRIM functions.
=SUMPRODUCT(-(LENGTH(TRIM(B3:B9))>0),D3:D9)
Let’s see step by step how the formula works.
First, the SUMPRODUCT function reads the cell values:
=SUMPRODUCT(-(LENGTH(TRIM({"A"; "B"; ""; "C"; ""; "XX"; " "}))>0),{25; 10; 15; 5; 8; 17; 50})
Then, the TRIM function removes spaces before and after the “Players” names:
=SUMPRODUCT(-(LENGTH({"A"; "B"; ""; "C"; ""; "XX"; ""})>0),{25; 10; 15; 5; 8; 17; 50})
The LENGTH function calculates the lengths of the trimmed “Players” names:
=SUMPRODUCT(-({1; 1; 0; 1; 0; 2; 0}>0),{25; 10; 15; 5; 8; 17; 50})
With the logical test (>0), all trimmed “Players” names with more than 0 characters are turned into TRUE:
=SUMPRODUCT(-({TRUE; TRUE ; FALSE; TRUE; FALSE; TRUE; FALSE}),{25; 10; 15; 5; 8; 17; 50})
Then, the double dashes (-) convert the TRUE and FALSE values into 1 and 0:
=SUMPRODUCT({1; 1; 0; 1; 0; 1; 0},{25; 10; 15; 5; 8; 17; 50})
The SUMPRODUCT function then multiplies each pair of entries in the arrays to produce an array of “Scores” only for “Players” names that are not empty or are not composed solely of spaces:
=SUMPRODUCT({25; 10; 0; 5; 0; 17; 0})
Finally, the numbers in the array are added together:
=57
More details on using boolean logic and the “-” sign in a SUMPRODUCT function can be found at Crawlan.com.
Sum If Not Blank in Google Sheets
These formulas work exactly the same way in Google Sheets as in Excel.
Voilà, mes besties! Now you know how to use the SUMIF function to add data related to non-empty cells in Excel and Google Sheets. Have fun and see you next time on Crawlan.com!
(Article based on the original content from automateexcel.com)
