In this tutorial, I will show you how to use the SUMIF Not Blank condition in Google Sheets and some examples so that you can get a better grasp on it.
Google Sheets has a lot of awesome features and functions that enable you to extract information even from huge datasets in an instant.
A common process done by a lot of users is to get the sum of values in a certain dataset.
Getting the sum is not always straightforward and, at times, requires conditions.
That is where the SUMIF Function comes in, but that being said, it’s still not as easy as just getting the sum as it is versus going a SUMIF Not Blank method where you only want to add values that pass the SUMIF Not Blank condition in a different array or column.
How to Use Google Sheets SUMIF Not Blank
- Identify the range to check for non-blank values
- Define the sum_range containing the values to be added if they match a non-blank value in the range
- Use the formula: “=SUMIF(range, “<>”, [sum_range])”
Using the Google Sheets SUMIF Not Blank Condition
SUMIF basically returns a sum across a range based on values meeting a specified criterion.
The syntax of SUMIF is
=SUMIF(range, criterion, [sum_range])
Where
- range – the range to check against the criterion (will also be the range to be summed if sum_range is undefined)
- criterion – the criterion to be checked against the range
- sum_range – [OPTIONAL] the range to be summed
The criterion parameter can be a string or a parameter and involved operators. For the Google Sheets SUMIF Not Blank condition, the criterion parameter has to be “<>.”
This gives us the Google Sheets SUMIF Not Blank formula to be
=SUMIF(range, “<>”, [sum_range])
Where “<>” is the operator symbol for “not equal to” and not indicating a number or string along with it means that we are defining the criterion as “not equal to blank”.
This means that as long as the value in the range is “not equal to blank”, the corresponding value (range or sum_range equivalent) will be summed – this is how the Google Sheets SUMIF Not Blank condition is met.
2 Examples of the Google Sheets SUMIF Not Blank Technique
I want to show you at least two examples of the Google Sheets SUMIF Not Blank criterion in action.
Similar to the screenshot below, these examples will show the SUMIF Function with the “<>” operator as its criterion.
Both examples are just simple applications of the Google Sheets SUMIF Not Blank criterion, but as with everything, practice makes perfect.
Example 1
In the example below, I created a sample dataset of ticket sales records per agent in a certain company. Their contract states that other than their commission of 50 per ticket sold, they will be assured of a Salary worth 200 as long as they sold at least one ticket.
The boss now wants to see how much she will pay out to each sales agent based on their performance in the last month.
That said, in the screenshot below, there are at least four agents who couldn’t sell a single ticket. This is why in their respective Tickets Sold rows, the cells are blank.
We want to give the boss the actual number for the upcoming payout. To do this, we will use the Google Sheets SUMIF Not Blank method, where as long as the Tickets Sold value is not blank, we’ll add the respective payouts.
The formula for this is “=SUMIF(C3:C12,”<>”,F3:F12)”.
Adding the formula into the sheet gave us the value of 2450, which has a difference of 800 from the initial 3250 total payouts.
That is the equivalent of 200 (Salary) x 4 (0 Tickets Sold Agents) – therefore, we can see that the only payouts that were summed are those that don’t have a blank value in the Tickets Sold column.
Example 2
For the next example, I have a list of students competing in the Earth Day Challenge, where they have to do certain tasks and also have the option to donate recyclables.
Part of the mechanics is that even though they can easily donate a lot of things for the competition, they must perform at least one cleanup, workshop, or seminar.
This is where we can use the Google Sheets SUMIF Not Blank criterion to identify the actual valid score per student and the total score for this particular class.
With that in mind, I used the formula: “=SUMIF(C3:C12,”<>”,E3:E12)”.
By doing this, I got the number 1300, which is a valid score for this class. There is a difference of 1500, which is the total score of Emmett and Carie, who did not perform any of the required events.
This is how the Google Sheets SUMIF Not Blank condition is used for these kinds of scenarios.
Frequently Asked Questions about How to Use Google Sheets SUMIF Not Blank
How can I use the Google Sheets SUMIF Not Blank condition?
You can use the Google Sheets SUMIF Not Blank condition by using the formula “=SUMIF(range, “<>”, [sum_range])”. Where the range parameter contains the values to be checked for blanks and sum_range contains the values to be summed respectively if they have a matching non-blank value in the range.
How can I use the Google Sheets SUMIF Not Blank condition to check multiple columns?
You can use the Google Sheets SUMIF Not Blank condition to check multiple columns by using the formula “=SUMIFS(sum_range, criteria_range1, “<>”, [criteria_range2, “<>”, …])”. The criteria_range parameters will contain the references for the columns to be checked and the sum_range will be the column to be summed.
Conclusion on How to Use Google Sheets SUMIF Not Blank
You can use the Google Sheets SUMIF Not Blank technique by first identifying the range to check for non-blank values and then defining the sum_range containing the values to be added if they match a non-blank value in the range. Lastly, use the formula: “=SUMIF(range, “<>”, [sum_range])”.
