What Is Excel SUMIF Not Blank?
The Excel SUMIF not blank function adds the values in a range based on the criterion that the corresponding cells in the same or another range are not empty. The formula involves the ‘<>’ operator as the SUMIF()’s second argument, criteria, to check for non-empty cells in the range specified as the first argument, range.
Users can utilize the SUMIF non-blank function to sum values in financial and accounting statements based on non-blank cells.
For example, the image below shows a dataset containing grocery items and their quantities.
The task is to calculate the total quantity of the grocery items listed in column A while ignoring the quantity values in rows where the item names are missing. Assume cell E1 is the target cell.
Then, we can apply the Excel formula SUMIF not blank, in the target cell to obtain the required total quantity.
The SUMIF not blank cells formula in the above example checks for the non-empty cells in the range A2:A9, based on the specified criterion, ‘<>’. The reason is that the ‘<>’ operator here interprets as not equal to blank.
Next, cells A2:A3, A5, and A7:A9 are not blank and hence meet the specified criterion. So, the SUMIF() sums the corresponding column B cells, B2:B3, B5, and B7:B9, to return the value of 16 as the required total quantity of non-blank grocery items.
Table of contents
- The SUMIF not blank 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 SUMIF non-blank formula in Excel to calculate sum values based on non-blank cells when working with financial and statistical data.
- The SUMIF not empty formula in Excel accepts two mandatory arguments, range and criteria, where the criteria argument value is typically the ‘<>’ operator. The function also accepts one optional argument, sum_range.
- We can apply the SUMIF not empty formula in Excel using VBA codes.
The SUMIF() syntax is as follows:
- range: The range of cells that we aim to get evaluated by the criterion.
- criteria: The criterion that decides which cells to add. 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 in the SUMIF() are compulsory, the third is optional.
Please note that if we omit the sum_range argument, Excel 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 Excel date format.
Furthermore, since the criterion to add a range of values is to check for non-blank cells in the range, the second argument in the SUMIF(), the criteria, will be the ‘<>’ operator. Thus, the Excel formula SUMIF not blank syntax is as follows:
Furthermore, the above syntax holds for Google Sheets SUMIF not blank as well.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use Excel SUMIF Not Blank?
While we can use Excel VBA SUMIF not blank code, the following two methods help us use the function more straightforwardly:
- Access from the Excel ribbon.
- Enter into the worksheet manually.
Method #1 – Access From The Excel Ribbon
Choose a cell for output → select the Formulas tab → click the Math & Trig group functions down arrow → select the SUMIF Excel function.
The Function Arguments window will appear. Enter the mandatory function arguments in the Range and Criteria fields, with the Criteria field value being ‘“<>”’. Next, enter the optional function argument in the Sum_range field if required.
Click OK in the Function Arguments window to exit from it and obtain the required sum value in the chosen cell.
Method #2 – Enter Into The Worksheet Manually
- Select a cell to show the output.
- Type =SUMIF( in the chosen cell. [Alternatively, enter =S or =SUM and double-click the SUMIF function from the suggestions Excel lists.]
- Enter the cell values and 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-blankfunction output in the chosen cell.
Please note that the above two methods will work for Google Sheets SUMIF not blank.
The following SUMIF not blank cells formula examples, will help us use the function effectively.
Example #1 – SUMIF Not Blank
The dataset below shows a student’s test scores in different subjects.
The aim is to add the test marks the student scored in the subjects that appear in column A while ignoring the test scores where the corresponding subject names are not available. Assume the target cell is B16.
While we can use Excel VBA SUMIF not blank, we shall directly insert the SUMIF non-blankformula in the target cell to obtain the required total test score.
- Step 1: Choose cell B16, enter the SUMIF not emptyfunction, and press Enter.
The function looks for the non-blank cells in the range A2:A14, based on the specified criterion, ‘<>’, which indicates a value not equal to blank.
Next, cells A2, A4:A6, A10:A11, and A14 are not blank and hence satisfy the given criterion. So, the SUMIF() adds the corresponding column B cells, B2, B4:B6, B10:B11, and B14, to return the value of 642 as the student’s total test score in the non-blank subjects.
Example #2 – Using Function Argument
The dataset in the image below lists a firm’s sales representatives, their zones and the sales they generated.
The requirement is to determine the total sales generated by only the sales representatives mentioned in column A. We must not consider those sales figures for which the representative names are missing in column A. Assume the target cell is F2, with the same data format as column C, Currency.
Then, here is how to use the SUMIF not emptyformula in the target cell to achieve the required outcome.
- Step 1: Choose cell F2 and select Formula → Math & Trig → SUMIF.
- Step 2: The Function Arguments window appears, where we must update the SUMIF not empty function arguments in the respective fields.
- Step 3: Click OK in the Function Arguments window to exit it and obtain the SUMIF non-blank formula output in the target cell.
The SUMIF() searches for the non-blank cells in the range A2:A17, according to the given criterion, ‘<>‘, which indicates a value not equal to blank.
Next, cells A2:A6, A9, A13:A15, and A17 are the non-empty cells. Thus, they satisfy the given criterion. So, the SUMIF() sums the corresponding column C cells, C2:C6, C9, C13:C15, and C17, to return the value of $51,330. Itis the total sales generated by the non-blank sales representatives.
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 emptyformula, 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 following Excel LEN function containing the Excel TRIM function, and press Enter.
- Step 3: Utilize the Excel fill handle to enter the formula in the remaining Helper Column cells.
- Step 4: Choose the target cell J2, enter the SUMIF not empty formula, and press Enter.
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)
SUMIF not blank VBA, is possible, as explained below with an example.
The source dataset lists candidates and the number of votes they won.
The aim is to add the votes of those candidates whose names are mentioned in column A while ignoring the votes of those candidates whose names are missing in column A. Assume the target cell is E2.
• Step 1: Keep the spreadsheet containing the above source dataset as the active sheet. Next, using Alt + F11, access the VBA Editor.
• Step 2: Choose the required VBAProject and select Module in the Insert tab.
A module window will appear.
• Step 3: Update the VBA code to apply the SUMIF not empty formula, in the target cell.
• Step 4: Press the play icon to run the code.
Now, open the active worksheet to view the total votes based on non-blank candidate names.
Excel SUMIF not blank is not working because of the following reasons:
• 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.
• The formula has type or syntax errors.
• The 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.
This article must be helpful to understand the SUMIF Not Blank, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to What Is SUMIF Not Blank. We learn to sum non-blank cells using SUMIF function and Function Argument, with examples. You can learn more from the following articles –