Excel Using COUNTIF and COUNTIFS

COUNTIF

If you are counting cells and only require one set of criteria COUNTIF should be your first option.

The syntax is very straight forward:

=COUNTIF(range, criteria)

Where your range is the group of cells you are comparing and the criteria equates to True or False. If your criteria is true for the cell within your range it is added to the count.

In the below spreadsheet I can work out how many employees are absent using this formula:

=COUNTIF(B2:B11,”TRUE”)

COUNTIF FORMULA EXCEL

or count how many employees reached their target of $300 with this formula:

=COUNTIF(C2:C11,”>300″)

COUNTIF EXCEL FORMULA

COUNTIF also works with a variety of operators and wildcards:

-Works out which employees were absent using not equals

=COUNTIF(B2:B11,”<>FALSE”)

-Counts the employees that have 9 within their SalesID

=COUNTIF(A2:A11,”*9*)             

-Counts the employees that have an ID ending in 5

=COUNTIF(A2:A11,”*5)                               

-Counts the employees that have an ID with 2 in at the 3rd character and 5 at the 5th character.

=COUNTIF(A2:A11,”??2?5?”)

 

COUNTIFS

If you are counting cells that are based on more than one column and require more than one criteria the simplest formula for the job will probably be COUNTIFS.

The syntax is slightly different to COUNTIF, the only difference is it allows for multiple ranges and multiple criteria:

=COUNTIFS( criteria_range1, criteria1, criteria_range2, criteria2, .. criteria_range_n, criteria_n )

Using COUNTIFS I can now work out how many employees were not absent and reached their given target of 300 using:

=COUNTIFS(B2:B11,”FALSE”,C2:C11,”>300″)

COUNTIFS FORMULA EXCEL

COUNTIFS is subject to the same rules as COUNTIF with criteria and will allow wildcards and operators.

I can further filter the previous example using COUNTIFS. The below formula will count the number of employees that have:

  • An ID with 2 in at the 3rd character and 5 at the 5th character
  • Only employees not absent
  • Who had sales over $300

=COUNTIFS(A2:A11,”???2?5?”, B2:B11,”FALSE”,C2:C11,”>300″)

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.