At times, there is a situation when we have to collect the data as per the specifications. 

Previously: How to create Course completion status summary in Excel?

You must be wondering what do we mean by SUMIF not blank. It’s obvious to you that SUMIF is a Function but by “not blank” we mean from the situations where the data is blank. To understand the same, here’s an example:

Blank Data with SUMIF formula

As you can observe in the image above, there is a column heading of “Supporting Department”. In the column, there are many cells are blank or in technical words, we can say that some of the projects were not supported by any department.

Blank Data with SUMIF formula

Now, let’s say we need a data or the total amount of all those projects which were supported by some departments and total amount of all those projects which were not supported by any department.

The formula for the same is:

=SUMIF(

The following formula requires range, criteria and sum range.

Blank Data with SUMIF formula

As our main findings are based on the projects which were supported and not supported, so our range will be the column of “Supporting Department” and not “Amount” column.

Blank Data with SUMIF formula

Then comes the criteria: 

Now if we are looking for the projects which were backed up by some departments then we will use the following:

“<>” 

Because it is difficult to mention all the departments or in other words it will be complicated for you!

By this, “<>” we mean that all those cells (data) will be selected which are filled with data, basically, which are not blank.

Blank Data with SUMIF formula

Then comes the sum range, now as we are looking for the total amount of the projects which are supported and not supported by various departments, so we will select the amount column as given in the mage above.

Blank Data with SUMIF formula

The answer came out, i.e. 3333000. So basically, it is the total amount of all those projects which were supported by some departments.

Now we come to the totalling of those projects which were not supported by any departments.

Blank Data with SUMIF formula

The formula will be the same, but when it will come to the point of criteria, instead of writing “<>” we will write only:

“”

By this, we can say that we have selected nothing, so it will track the data of all those cells which are blank. 

“<>” this means that we are selecting something, the brackets inside signify that those cells will be observed which contains any type of a data in that particular range.

“” this means blank, i.e. nothing selected, so it will target all those cells which are left blank and will observe their data.

Blank Data with SUMIF formula

After criteria, we will select the sum range, i.e. the amount in this case.

Blank Data with SUMIF formula

The answer came out, i.e. 805000, this is the total amount of all those projects which were not supported by any department.

Blank Data with SUMIF formula

To verify for whether this data is true or not, we will use the SUM Function and we will select both the answers, as shown in the image above.

Blank Data with SUMIF formula

Now press enter, the total amount will come out as 4138000, which matches your totals of the all project’s amounts.

Blank Data with SUMIF formula

It’s easy, isn’t it?