To count the non-blank categories and conclude the results or in other words bring summary to something, here’s what we can do:
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.
Now to do the same, we will be using the COUNTIFS Function.
The following function requires, criteria range 1, criteria 1, and so on…
So, the first criteria range in the example above is the product. So, we will select the products i.e. B3:B9.
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).
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.
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.
Then we will close the COUNTIFS Function.
Overall, our formula becomes as follows:
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.
Now we move to the aspect of approval in reference to the products.
We will again use the COUNTIFS Function only.
The criteria range 1 will be the product range only (as shown in the image above).
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.
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.
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.
So overall, our formula becomes as follows:
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.
It’s easy, isn’t it?