Excellency

View Original

The COUNTIFS function

The COUNTIFS function is a statistical function which is identical to the similarly named COUNTIF function with the only difference being that it can handle multiple sets of criteria instead of the singular one COUNTIF can handle. Following the same logic as most of the other IF related functions, the only cells counted are the ones which meet the criteria requested.

This is the format of the COUNTIFS function:

=COUNTIFS(CRITERIA RANGE 1, CRITERIA 1, CRITERIA RANGE 2, CRITERIA 2, etc…)

Here is an example of the COUNTIFS function:

=COUNTIFS(A1:A6, “A”, B1:B6, “>3”) = 2

COUNTIFS returning a count of the rows which have both the letter “A” and a value greater than 3

This function is valuable when taking stock of a series of requirements or eligibility to determine the number of occurrences within your dataset. It is valuable when attempting to determine if your requirements are too lenient or stringent against acceptance/rejection rates.

An example of a functional implementation of this would be determining the bonus structure for salespeople within a company. If your aim is to have 20% receive a tier 1 bonus, 30% a tier 2 bonus, 30% a tier 3 bonus, and the bottom 20% no bonus, you can use COUNTIFS to determine how many salespeople would be eligible based on the previous years sales history. This would help with budgeting for the bonus’ and anticipating future results.

=COUNTIFS(COLUMN WITH BONUS ELIGIBILITY, “Y”, COLUMN WITH TOTAL SALES, “>10000”)
=COUNTIFS(COLUMN WITH BONUS ELIGIBILITY, “Y”, COLUMN WITH TOTAL SALES, “>25000”)
=COUNTIFS(COLUMN WITH BONUS ELIGIBILITY, “Y”, COLUMN WITH TOTAL SALES, “>60000”)
=COUNTIFS(COLUMN WITH BONUS ELIGIBILITY, “Y”, COLUMN WITH TOTAL SALES, “>100000”)
See this content in the original post