Quick, clean, and to the point

Summary count of non-blank categories

Excel formula: Summary count of non-blank categories
Generic formula 

To build a summary count of non-blank categories, you can use the COUNTIFS function.

In the example show, the formula in G6 is:


This example relies on core COUNTIFS functionality.

To create a summary count by building, we would use COUNTIFS like this:


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:


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:


The first range/criteria pair is the same. The second range/criteria pair simply counts non-blank entries in the approval column (D5:D11).

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, 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 how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.