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

Formulas are the key to getting work done in Excel. In this accelerated video course, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training you should have had to begin with. See details here.

I sincerely want to register my profound gratitude to Dave and Lisa. There are many features so unique about the tutorials in Exceljet: the simplicity, down-to-earth approach and the connection between the instructor and the learner. -John
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