What Is SUMIF Not Blank In Google Sheets?
The SUMIF Not Blank in Google Sheets only adds the numeric values in a selected cell range based on the criterion that the corresponding cells of the cell’s numeric values are not empty. Users can utilize the Google Sheets SUMIF not blank to find the total of the financial and accounting statements based on non-blank cells.
For example, the dataset below contains the items purchased, their quantities and the sum of items purchased calculated in cell B10.
Let us calculate the Google Sheets SUMIF not blank, to find the total of only the quantity of the ones with their corresponding item names. Therefore, select cell E1, enter the formula =SUMIF(A2:A9,”<>”,B2:B9) and press “Enter”, as shown below.
We can see that the total number of items purchased is 47. However, the output is calculated as 32. It is due to the operator ‘<>’ that interprets as not equal to blank and helps the SUMIF not blank cells formula to check only for the values with the corresponding items present. Therefore, it excluded the B4+B6 values, i.e., 6+9 = 15. Hence, it is 47-15 = 32.
Table of Contents
Key Takeaways
- The SUMIF Not Blank in Google Sheets formula adds numbers in a range based on the corresponding non-blank cells in the same or another range, if specified.
- Users can use the formula to calculate sum values based on non-blank cells when working with financial and statistical data.
- The formula accepts two mandatory arguments, range and criteria (the criteria argument value is typically the ‘<>’ operator) and one optional argument, sum_range.
- We can use some of the other functions such as LEN(), TRIM(), etc., along with the SUMIF() to calculate the SUMIF not blank.
Syntax
The syntax of the SUMIF() formula in google sheets is:
The arguments of the SUMIF() formula in google sheets are,
- range: The selected cell range or dataset to find the sum as it satisfies the criteria.
- criterion: It decides the cells to add and the ones to be ignored. The argument value can be a number, a cell reference, text, or a function.
- [sum_range]: The range of cells containing the values to add if we wish to sum cells other than those cited in the range argument. Further, the sum_range size should be equal to the range size. Otherwise, the formula will add a range of cells starting with the first cell in sum_range but having identical dimensions as the range.
While the first two arguments are mandatory, the third is optional.
[Note: If we ignore the sum_range argument, Google Sheets will add the cells provided in the range argument (the same cells to which the criterion is applied). Thus, in such scenarios, the cells in the specified range should be numbers, arrays, names, or references containing numbers. On the other hand, blanks and text values get ignored. Also, the range may contain date values in the valid Google Sheets date format.
How To Use SUMIF Not Blank In Google Sheets?
We can use the Google Sheets SUMIF Not Blank in the following two methods, namely,
- Access from the ribbon.
- Enter into the worksheet manually.
Method #1 – Access from the ribbon 🡢
Choose an empty cell for output and select the “Insert” tab. Click the “Function” option right arrow and click the “Math” option right arrow. Select the “SUMIF” function, as shown below.
The SUMIF formula appears as shown below.
Select the arguments values in the Range, Criteria and Sum_range (if required) with the Criteria field value being “<>” and press “Enter”.
Method #2 – Enter in the worksheet manually 🡢
- Select an empty cell to display the output.
- Type =SUMIF( in the chosen cell. [Alternatively, enter =S or =SUM and click the SUMIF function from the suggestions Google Sheets lists.]
- Enter the cell values or references as the argument values. Ensure to supply the ‘<>’ operator in double quotes if supplying it directly as the criteria argument value.
- Close the brackets and press Enter to obtain the sum value as the SUMIF non-blank function output in the chosen cell.
Examples
We will consider some examples of SUMIF not blank in Google Sheets to understand the function better.
Example #1 – SUMIF Not Blank
The dataset below shows a student’s test scores in different subjects and the total marks calculated in cell C12. We will add the marks scored while ignoring the scores where the corresponding subject names are not available.
The steps to calculate using the SUMIF Not Blank formula are as follows:
Step 1: Choose cell C14 and enter the formula =sumif(B2:B11,”<>”,C2:C11).
Step 2: Next, press the “Enter” key. We get the calculated output as 684, as shown below.
The function looks for the non-blank cell values in the range C2:C11, based on the specified criterion, ‘<>’ and returns the value of 684 as the student’s total test score in the non-blank subjects.
Example #2 – Using Function Argument
The dataset given below is a firm’s monthly sales generated and the total of the sales calculated in cell B14.
The steps to calculate the total sales generated by the non-blank cells using the SUMIF function are as follows:
Step 1: Choose cell F1 à select the “Insert” tab à click the “Function” option right arrow à click the “Math” option right arrow à select the “SUMIF” function, as shown below.
Step 2: The SUMIF formula appears in cell F1. Now, select the cells A2:A13, type “<>” and select the cells B2:B13 in place of the arguments, as shown below.
Step 3: The formula now is =SUMIF(A2:A13,”<>”,B2:B13). Press the “Enter” key to get the SUMIF non-blank formula output in the target cell, as shown below.
Example #3
The following dataset contains a list of products and their order details.
The requirement is to calculate the total order cost based on non-blank product IDs while not considering the order cost values for which the corresponding product IDs are missing. Assume the target cell is I2, with the same format as column E containing the order cost values.
Then, here is how to apply the SUMIF not empty formula, without using the ‘<>’ operator as the criterion, in the target cell to get the required output.
Step 1: Insert a Helper Column after column B, containing the product IDs in a few cells.
Please note that since we inserted a new column in the source dataset, the new target cell is cell J2.
Step 2: Choose cell C2 in the Helper Column, enter the formula =LEN(TRIM(B2)) and press Enter.
Step 3: Drag the formula from cell C2 to C11 using the fill handle.
Step 4: Choose the cell J2, enter the SUMIF not empty formula =SUMIF(C2:C11,“>0”,F2:F11) and press Enter.
Output Observation:
- First, the TRIM() in the Helper Column cell removes all the spaces from the specified product ID except the single spaces between the words.
- Next, the LEN() returns the total number of characters in the TRIM() output. If a column B cell contains a product ID, the Helper Column formula will return a non-zero value. On the other hand, if the column B cell is blank, the Helper Column formula will return zero.
- Next, the SUMIF() checks for cells containing a value greater than 0 in the range C2:C11, the Helper Column cells. Cells C2, C4:C5, C7:C8 and C11 meet the specified criterion. So, the SUMIF() adds the corresponding column F cells, F2, F4:F5, F7:F8 and F11 to return the value of $15,200 as the required total order cost based on non-blank product IDs.
Important Things To Note
- If we supply the ‘<>’ operator directly as the SUMIF not blank formula argument value criteria, it should be within double quotations. Otherwise, the formula will not work.
- The sum_range size should be equal to the range size. Otherwise, the formula will add the cells in the range that commences with the sum_range’s first cell and has the same dimensions as the range.
- The values in the range or sum_range, if specified, in the SUMIF non-empty formula must be numbers. Otherwise, the formula output will be incorrect.
Frequently Asked Questions (FAQs)
There are times when we cannot remember in which category the “SUMIF” function falls. Then, we can insert the function as follows:
Choose an empty cell ➛ select the “Insert” tab ➛ click the “Function” option right arrow ➛ click the “All” option right arrow ➛ select the “SUMIF” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
A few reasons the Google Sheets SUMIF not blank may not work are,
The range or sum_range values we aim to add are not numbers.
The ‘<>’ operator supplied directly as the criteria argument value to the SUMIF() is not in double-quotes.
Its formula has type or syntax errors.
Parameter sum_range size is not the same as that of range.
The difference between SUMIF not blank and SUMIFS not blank VBA is that the SUMIF not blank formula in VBA adds values based on one criterion, which is non-blank cells. On the other hand, the SUMIFS not blank formula in VBA also adds values based on non-blank cells. However, the function can include one or more conditions to check simultaneously, along with non-blank cells, to add the required values.
Download Template
This article must help understand SUMIF Not Blank in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is SUMIF not Blank In Google Sheets. We learn its syntax and how to use it to find sum of nonblank cell values with examples. You can learn more from the following articles –
SUMIF Between Two Dates In Google Sheets
Leave a Reply