14,534
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
