## What Is Excel SUMIF Not Blank?

The Excel

SUMIFnot 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 theSUMIF()’s second argument,criteria, to check for non-empty cells in the range specified as the first argument,range.Users can utilize the

SUMIFnon-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

###### Key Takeaways

- 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.

### Syntax

The **SUMIF() **syntax is as follows:

Where,

**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:

**=SUMIF(range,“<>”,[sum_range])**

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)**

**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**.

### Examples

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**.

*=SUMIF(A2:A14,”<>”,B2:B14)*

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.

#### 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 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**.

*=LEN(TRIM(B2))*

**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**.

*=SUMIF(C2:C11,”>0″,F2:F11)*

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)

**1. Is SUMIF not blank VBA possible?**

**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.

**2. Why is Excel SUMIF not blank not working?**

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**.

**3. What is the difference between SUMIF not blank and SUMIFS not blank VBA?**

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 be helpful to understand the **SUMIF Not Blank**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

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 –

## Leave a Reply