< Go Back

Summary count of non-blank categories

Summary Count Of Non-Blank Categories With COUNTIFS

What COUNTIFS?

COUNTIFS function This is one of the statistical function that counts the number of cells in a range. However, the function counts cells that meet single or multiple criteria. The function can also be used to count non-blank cells/category.

Generic Formula

=COUNTIFS(range1, criteria1, range2, “<>”)

Example of How COUNTIFS Function Works

To get a summary count of non-blank categories with COUNTIFS function, the process is very simple. In this example, we are going to look at a summary of three construction using the COUNTIFS function: Check the screenshot below:

Figure 1. Example 1 of COUNTIFS Function

How COUNTIFS Formula Works

From this example, the summary count of non-blank categories works is pretty simple. The formula in H6 is:

=COUNTIFS($B$5:$B$11,G6)

From the generic formula, the criteria_range1 is B5:B11. It returns a count of matching sites on column B only.

To count site inspection, the formula is extended by an additional range/criteria pair which looks like this

=COUNTIFS($B$5:$B$11,G6,$C$5:$C$11,“<>”)

….in this, the criteria_range2 C5:C11, which is an absolute reference, and criteria2 is “<>” meaning “not empty” or “not blank.” So the formula in G6, it can be translated to count of entries where the site is J and inspection is not blank.

Similarly, we can count approvals per construction site using COUNTIFS Formula in I6:

=COUNTIFS($B$5:$B$11,G6,$D$5:$D$11,“<>”)

You will notice that the first range/criteria pair is the same, but the second pair counts non-blank entries in D column D (D5:D11).

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar