SUMIF Not Blank

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Excel SUMIF Not Blank?

SUMIF not blank in excel is a very simple function used to find the total of a specific criteria in the data. First, we use “ “ as a criterion for a blank cell. But, to use SUMIF when only the cells are not blank as the criteria, we can use the operator “<>,” which means not equal to blank. This operator acts as the criteria for the function in summing up the cells when the criteria range is not blank.

For example, consider the below table showing the highest marks obtained by students in 4 semesters. Now, assume that we have to calculate the sum of all the highest marks in semester 1.

SUMIF Not Blank - Intro

The steps are:

Step 1: First, select the cell where we want to insert the result. So, in this example, select cell E4.

Step 2: Then, enter the arguments in the Function Arguments window.

Step 3: Click OK. We can immediately see the results in cell E4.

SUMIF Not Blank - Intro - Output.jpg

Likewise, we can use SUMIF not blank in Excel.

In this article, we will learn how to use SUMIF not blank and how to select the values for arguments with examples.

Key Takeaways

  • SUMIF not blank in Excel is a formula used to find the total based on the criteria.
  • The formula of SUMIF is =SUMIF(range,criteria,[sum_range]) where all 3 arguments are mandatory.
  • In Range argument, we have to select the cell range with different criterias.
  • Similarly, we have to select the cell specifying the criteria based on which the total sum is calculated.
  • Finally, sum_range is the argument where we have to select the cell range with the values.
  • We can simply click on Formulas – Math & Trig – SUMIF to insert the formula in Excel.

Syntax

The syntax of SUMIF is =SUMIF(range,criteria,[sum_range]) where all 3 arguments are mandatory.

SUMIF Formula.jpg

–>> 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?

This article must be helpful to understand the Excel SUMIF Not Blank, with its formula and examples. You can download the template here to use it instantly.

Examples

Example #1 – SUMIF Not Blank

The below set of data contains certain blanks in the range criteria. Suppose we still want to know the total no. of passengers:

SUM not Blank Example 2

Here now, criteria range conditions completely change for both by city and month. Important to note the condition which we put here in the syntax for criteria:

  • <>” -> indicates the “NOT EQUAL TO” sign. It has to be in double inverted commas as the formula processes it as characters. When we use this, the formula sums up all the values that are not blanks and completely ignores the blank cells during summation.
  • “-> indicates “Blanks.” Double inverted commas with no characters in them signify equal to blank. When we use this, it sums up all the values containing blanks and ignores cells containing some characters/values.

For the ease of understanding the final output of tables 1 and 2. Refer to the below explanation:

SUM not Blank Example 2-1

Example 2 – Using Function Argument

Let us look at the example.

  1. Select cell F2(active cell), where we need SUMIF() value as shown below:


    SUM not Blank Example 3

  2. Click on the “Formulas” tab and select “Math & Trig.”


    SUM not Blank Example 3-1

  3. A dropdown appears with various mathematical functions in Excel. For example, click “SUMIF”(highlighted in red).


    SUM not Blank Example 3-2

  4. A dialog box appears, as shown below. The next steps would be to input corresponding respective cell references or values:


    SUM not Blank Example 3-3

    Here, we will insert the cell reference of the “City” column in “Range” input, “City name/ cell reference” in “Criteria” input, and “No. of Passenger” in “Sum_range.”

  5. Go to the “Range” section in the dialog box, click in the blank space, select range starting from A2, and drag up to A17. Next, select all the row ranges in “City” column B. Then, go to the “Criteria” section and select the “City” cell reference, cell E2. Similarly, go to the “Sum_range” section in “Column C” and choose from C2 to C17.


    SUM not Blank Example 3-4

  6. Click on “OK” and see the value in cell E2.


    SUM not Blank Example 3-5

Example 3

SUMIF Not Blank - Example 3
  • Select cell E4(active cell), where we need SUMIF() value as shown below:
  • Click on the “Formulas” tab and select “Math & Trig.”
SUMIF Not Blank - Example 3 - Math-Trig.jpg
  • A dropdown appears with various mathematical functions in Excel. For example, click “SUMIF”(highlighted in red).
SUMIF Not Blank - Example 3 - Step 3.jpg
  • A dialog box appears, as shown below. The next steps would be to input corresponding respective cell references or values:
Example 3 - Step 4.jpg

Here, we will insert the cell reference of the “Subject” column in “Range” input, “Physics” in “Criteria” input, and “Marks” in “Sum_range.”

Example 3 - Step 4 - Function Argument.jpg
  • Click on “OK” and see the value in cell E4.
Example 3 - Step 5.jpg

Important Things To Note

  • The SUMIF Not Blank has limited use unless that dataset contains blank rows in the criteria range.
  • We have to specify the cell range properly to obtain proper results.

Frequently Asked Questions

1. What is SUMIF not blank in excel?

SUMIF not blank in excel is a function used to find the total based on a criteria in the data. Sometimes we have a dataset that contains blank inputs. However, we have a corresponding value assigned to it. In such cases, we may want to exclude the cells that contain blank inputs and do the total. In such cases, we use the criteria of the “SUMIF Not Blank.”

2. Explain the use of SUMIF not blank with an example.

Consider the below table showing the sales of products. Now, assume that we have to calculate the sum of product C’s sales.

FAQ 2.jpg

The steps are:

Step 1: First, select the cell where we want to insert the result. So, in this example, select cell E4.

Step 2: Then, enter the arguments in the Function Arguments window.

Step 3: Click OK. We can immediately see the results in cell E4.

FAQ 2 - Output.jpg

Likewise, we can use SUMIF not blank in Excel.

3. How to insert SUMIF not blank formula?

To use or insert SUMIF not blank in excel, we need to click on Formulas – Math & Trig – SUMIF.

Recommended Articles

This article has been a guide to the SUMIF Not Blank. Here, we discuss how to add cells based on blank / not blank criteria using SUMIF, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *