At times, there is a situation when we have to collect the data as per the specifications.
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:
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.
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:
The following formula requires range, criteria and sum range.
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.
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.
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.
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.
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.
After criteria, we will select the sum range, i.e. the amount in this case.
The answer came out, i.e. 805000, this is the total amount of all those projects which were not supported by any department.
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.
Now press enter, the total amount will come out as 4138000, which matches your totals of the all project’s amounts.
It’s easy, isn’t it?