Lesson 23: COUNTIFS Function (Excel 2007)

	A	B	C	D
1	Month	State	Name	Amount
2	5/1/2009	MI	John	2
3	6/1/2009	NY	Peter	6
4	6/1/2009	PA	John	8
5	5/1/2009	NH	Peter	3
6	6/1/2009	MI	John	7
7	5/1/2009	FL	Peter	6

The general format for the COUNTIFS formula is as follow: =COUNTIFS(range to COUNT, range of criteria 1, criteria 1, range of criteria 2, criteria 2) and you can have as many criteria as you need.

It is a little different then the CONTIF function that that could only handle a single criteria and that it replaces.

In this first example the criteria applies :
=COUNTIFS(D2:D7,D2:D7,">5")
In plain English it says: Count the values of the cells D2 to D7 if they are greater than 5. The result should be 3.

You can have many critEria like in the example below:
=COUNTIFS(D2:D7,D2:D7,">5",C2:C7,"John") which results in 2 because it is counting the values in D2 to D7 greater than 5 if the name in C2 to C7 is John.
In the above formula you can replace John by its address:
=COUNTIFS(D2:D7,D2:D7,">5",C2:C7,C2)

SUMPRODUCT can do More

If you want to sum the amounts based on its value, on the name AND on the month, you can not use COUNTIF or COUNTIFS have to use SUMPRODUCT. Here is an example:
=SUMPRODUCT((MONTH(A2:A7)=6)*(C2:C7="John")*(D2:D7>5))
The formula above sums all value grater than 5 in D2 to D7 if the value in C5 to C7 is John and if the month of the date in A2 to A7 is 6 (June).
You could not do the above with COUNTIFS

WARNING: You can only use COUNTIFS if you are using Excel 2007 and if everyone who will receive your workbook are also using Excel 2007. You can do the same thing and even more in a version of Excel that is earlier than 2007. You will use SUMPRODUCT the most powerful function in Excel.

The COUNTIFS Function in Excel
Links to the Top of the Page

The COUNTIF Function in Excel

The SUMPRODUCT Function in Excel

Excel Functions and Formulas Sitemap

Downloadable Tutorials, Examples and Templates