Exceljet

Quick, clean, and to the point

Summary count of non-blank categories

Excel formula: Summary count of non-blank categories
Generic 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).

Author 
Dave Bruns

Excel Formula Training

Learn Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet and audio transcript. Instant access and complete 100% guarantee. Start today!

See details.

Just wanted to say "Thank you" and I feel lucky I found your website! I work with contracts and your link lead me to a concise answer. No other link came close. -Karen
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course