To count the non-blank categories and conclude the results or in other words bring summary to something, here’s what we can do:

Example:

summary count of non-blank categories

As you can observe in the image above, we have taken three products 201, 202, and 203. These products are inspected as well as approved. But to keep a count on the number of times it has been inspected and approved, we create a summary.

summary count of non-blank categories

Now to do the same, we will be using the COUNTIFS Function.

=COUNTIFS(

The following function requires, criteria range 1, criteria 1, and so on…

Previously: How to get the value of the first match in a range using a wildcard? 

So, the first criteria range in the example above is the product. So, we will select the products i.e. B3:B9.

summary count of non-blank categories

Then after adding the criteria range 1, we will hop on to criteria 1, as we are looking for product 201 currently, so that will be our criteria 1 (cell G4).

summary count of non-blank categories

Then we come to the aspect of criteria range 2. As we are looking for the count over the inspection made over the product 201, so inspection range will be out criteria range 2, i.e. C3:C9.

summary count of non-blank categories

Then we come to criteria 2, as we are talking about inspection and we need to look after non-blank categories, so we will input this:

“<>” in our criteria 2.

*please note: do not forget to add the quotation marks as we are using specifications in the formula i.e., we are looking for count on non-blank categories only.

summary count of non-blank categories

Then we will close the COUNTIFS Function.

Overall, our formula becomes as follows:

=COUNTIFS(B3:B9,G4,C3:C9,”<>”)

summary count of non-blank categories

Now, all we have to do is hit enter. And BAAAMMMM!!! The answer is on your screen. The product 201 has been inspected 2 times. Now for the rest of the products, all you have to do is drag down the formula to get things done easily instead of jotting down the COUNTIFS Function for every product.

summary count of non-blank categories

Now we move to the aspect of approval in reference to the products.

We will again use the COUNTIFS Function only.

summary count of non-blank categories

The criteria range 1 will be the product range only (as shown in the image above).

summary count of non-blank categories

Now, we will select the criteria 1, as we are looking product 201 from the product range, so product 201 will be our criteria 1 i.e. cell G4.

summary count of non-blank categories

Then we come to the aspect of criteria range 2. As we are talking about products that have got the approval so our criteria range 2 will be the Approval range i.e. D3:D9.

summary count of non-blank categories

Our criteria 2 will be:

“<>”

As we are looking for count on non-blank categories. So, these symbols <> indicate that characters are involved and quotation marks are necessary because we are adding a particular type of specification in the formula.

summary count of non-blank categories

So overall, our formula becomes as follows:

=COUNTIFS(B3:B9,G4,D3:D9,”<>”)

summary count of non-blank categories

Now all we have to do is press enter. And BAAAMMM!!! Your answer is right on the screen. Now all you can do is drag down the formula for the rest of the products and save yourself from inputting the COUNTIFS Function for every product.

summary count of non-blank categories

It’s easy, isn’t it?