423,846 Members | 2,048 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How to use the COUNTIF, COUNTIFS and SUMPRODUCT Worksheet Functions in Excel

MMcCarthy
Expert Mod 10K+
P: 14,534
These three Worksheet functions preform similar tasks but there are key differences between them. The COUNTIFS function is only available in Excel 2007 and later.

COUNTIF()
The COUNTIF function is used to "Count" instances of a set criteria in a given range. So you are counting the number of times your given criteria is true in the cells of whatever range you set.

The syntax for the function is:
COUNTIF ( Range, Criteria )

In this excample given below you can see the formula I used to test the function. In Cell Reference D2 I inserted the COUNTIF function to give me a count of the number of times in the Date_Entered (A2:A6) column the date was greater than 01/01/1980.




COUNTIFS()
The COUNTIFS function is used to do a similar job to the COUNTIF function except you can assign multiple criteria accross multiple ranges. However, all checks must be true before the count is incremented.

The syntax for the function is:
COUNTIFS ( Range, Criteria, Range2, Criteria2 ...[Range_n],[Criteria_n] )

In the first example below I use the same range for both criteria, this allows me to return a count of cells in that range that meets more than one criteria. Please note both criteria must be true. So I get a count of all cells in the Date_Entered (A2:A6) column where the date is between 01/01/1980 and 01/01/1990.



In the second example I add a second range with it's own criteria. So this time I get a count of the cells in the Date_Entered (A2:A6) column where the date is between 01/01/1980 and 01/01/1990 AND the Score (B2:B6) is greater than 75.




SUMPRODUCT()
The SUMPRODUCT function can be used instead of COUNTIFS if your Excel version is less than 2007. However, the formula is quite complicated. The SUMPRODUCTS function is more commonly used to multiply the corresponding values in two arrays. The arrays must be the same size and position as each other.

The syntax for the function is:
SUMPRODUCT ( Array_1, array_2, ...[Array_n] )

The first example shows the use of SUMPRODUCT to achieve a similar result to the COUNTIFS function. I have used the DATE function to represent the dates as the SUMPRODUCT function won't recognise a date directly entered.



The second example shows the more common usage of the SUMPRODUCT function to multiply corresponding values in two or more arrays. In this case (1*4) + (2*6) + (3*8) + (4*3) + (5*9) + (6*2) = 109.




Mary McCarthy
Bytes Admin
Attached Images
File Type: jpg CountIfs_1.jpg (74.3 KB, 11115 views)
File Type: jpg CountIfs_2.jpg (68.8 KB, 13374 views)
File Type: jpg SumProduct_1.jpg (60.1 KB, 10514 views)
File Type: jpg SumProduct_2.jpg (75.9 KB, 10306 views)
File Type: jpg Countif.jpg (86.7 KB, 17678 views)
May 20 '13 #1
Share this Article
Share on Google+