This tutorial shows Summary count of non-blank categories in Excel using the example below;
Formula
=COUNTIFS(range1,criteria1,range2,"<>")
Explanation
To build a summary count of non-blank categories, you can use the COUNTIFS function.
In the example show, the formula in G6 is:
=COUNTIFS($B$5:$B$11,F6,$C$5:$C$11,"<>")
How this formula works
This example relies on core COUNTIFS functionality.
To create a summary count by building, we would use COUNTIFS like this:
=COUNTIFS($B$5:$B$11,F6)
Where the criteria_range1 is B5:B11 (locked as an absolute reference so that the formula can be copied down the table and criteria1 is pulled from column F. This will return a count of all matching buildings based on column B only.
To count inspections by building, we need to extend the formula by adding an additional range/criteria pair like this:
=COUNTIFS($B$5:$B$11,F6,$C$5:$C$11,"<>")
Here, criteria_range2 is C5:C11 (as an absolute reference) and criteria2 is “<>”, which means “not blank” or “not empty”. For the formula in F6, this translates to: count of entries where building is A and inspection is not blank.
In a similar way, to count approvals per building, we use this formula in H6:
=COUNTIFS($B$5:$B$11,F6,$D$5:$D$11,"<>")
The first range/criteria pair is the same. The second range/criteria pair simply counts non-blank entries in the approval column (D5:D11).