468,117 Members | 1,463 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

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

MMcCarthy
14,534 Expert Mod 8TB
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, 12453 views)
File Type: jpg CountIfs_2.jpg (68.8 KB, 14873 views)
File Type: jpg SumProduct_1.jpg (60.1 KB, 11254 views)
File Type: jpg SumProduct_2.jpg (75.9 KB, 11020 views)
File Type: jpg Countif.jpg (86.7 KB, 20512 views)
May 20 '13 #1
0 20772

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
1 post views Thread by Rene | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.