14. Exercise – COUNTIF, COUNTIFS, SUMIF and SUMIFS functions
Prerequisites
You should read the Excel Functions course up to the chapter on COUNTIF, COUNTIFS, SUMIF and SUMIFS functions before starting this exercise.
Question
Consider the spreadsheet extract representing a list of members of an association:
Question 1
On another sheet of the same workbook, the number of subscribers per city on the one hand and per city per year on the other hand is calculated:
- Write the formula in B3 to calculate the number of members per city from the list in the first sheet. The range B3:B9 must be filled in by copying the formula in B3 by Autofill.
- Write the formula in C3 to calculate the number of members per city per year from the list in Sheet1. The range C3:F9 must be filled in by copying the formula in C3 by Autofill.
Question 2
On another sheet of the same workbook, the sum of donations per city on the one hand and per city and per sector on the other hand is calculated:
- Write the formula in B3 to calculate the sum of donations per city from the list in Sheet1. The range B3:B8 must be filled in by copying the formula in B3.
- Write the formula in C3 to calculate the sum of donations per city and per sector from the list in Sheet1. The range C3:H8 must be filled in by copying the formula in C3.
You can download the file for this exercise here.
Indications Exercise – COUNTIF, COUNTIFS, SUMIF and SUMIFS functions
Question 1
Solution :
Formula in B3 of sheet 2, to be copied to range B3:B9
=COUNTIF(Sheet1!H$2:H$1475, A3)
Formula in C3 of sheet 2, to be copied to range C3:F9
=COUNTIFS(Sheet1!$H$2:$H$1475, $A3, Sheet1!$I$2:$I$1475, C$2)
Formula in I2 of sheet 1, to be copied to range I2:I1475
=YEAR(F2)
Explanation :
For the formula in B3 of sheet 2, the function COUNTIF counts the occurrences of the value in A3, i.e. "London" in the range H2:H1475 of sheet 1. The absolute reference is necessary for the row number and optional for the column number because the copy will be done vertically.
Concerning the formula in C3 of sheet 2, we have 2 criteria which are the city and the year. We must therefore use the function COUNTIFS.
On the other hand, since sheet 1 does not contain the year of membership of the subscribers, we have added this information to column I. The function YEAR gives the year for the date given as an argument, i.e. the membership date entered in column F.
Question 2
Solution :
Formula in B3 of sheet 3, to be copied to range B3:B9
=SUMIF(Sheet1!H$2:H$1475, A3, Sheet1!G$2:G$1475)
Formula in C3 of sheet 2, to be copied to range C3:H9
=SUMIFS(Sheet1!$G$2:$G$1475, Sheet1!$H$2:$H$1475, $A3, Sheet1!$E$2:$E$1475, C$2)
Explanation :
As regards the formula in B3 of sheet 3, the SUMIF function counts the sum of the values in column G whose corresponding values in column H are equal to the value in A3 of sheet 3, i.e. "London". The absolute reference is necessary for the row number and optional for the column number because the copy will be done vertically.
As regards the formula in C3 on sheet 3, we have 2 criteria which are the city and the year. We must therefore use the function SUMIFS.
You can download the file filled in with these formulas here.