Excel formula practice: SUMIFS, COUNTIFS, AVERAGEIFS, XLOOKUP
Conditional sums, counts, averages and lookups on real-looking business tables, with the formulas. Updated 3 November 2026.
Steps
- Download the file for each question (employees, invoices, students or products).
- Type the formula yourself before you open the answer.
- Column letters in our formulas match the downloaded file, which starts in cell A1 with headers in row 1.
Questions
try first, then open the answer
How many employees are in each department?
Engineering 231; Customer Support 157; Sales 157; Operations 138; Finance 98; Marketing 92; IT 73; Human Resources 54
Excel
=COUNTIF(E:E,"Engineering")File: employees-1000
Average salary of full-time employees by department (rounded to the dollar)?
Engineering $121,001; Finance $89,073; IT $82,218; Marketing $77,458; Sales $75,110; Human Resources $74,481; Operations $72,040; Customer Support $67,499
Excel
=AVERAGEIFS(H:H,E:E,"Sales",K:K,"Full-time")File: employees-1000
How many people were hired in 2020 or later?
396 employees
Excel
=COUNTIFS(G:G,">="&DATE(2020,1,1))File: employees-1000
What share of employees have a performance rating of 4 or 5?
38.9%
Excel
=COUNTIF(L:L,">=4")/COUNTA(A2:A1001)File: employees-1000
How much money is overdue?
$506,974.73
Excel
=SUMIFS(I:I,J:J,"Overdue")File: invoices-1000
How many students got each letter grade?
A 78, B 274, C 379, D 226, F 43
Excel
=COUNTIF(J:J,"A")File: students-1000
Average math score by grade level?
grade 9: 76.8, grade 10: 74.5, grade 11: 77.0, grade 12: 75.7
Excel
=AVERAGEIF(D:D,9,F:F)File: students-1000
How many students have attendance below 90%?
139 students
Excel
=COUNTIF(K:K,"<90")File: students-1000
How many customers are active (ordered in 2024)?
469 of 1,000
Excel
=COUNTIF(M:M,TRUE)File: customers-1000
Average lifetime spend by segment?
Enterprise $13,507.62; Small Business $1,866.74; Consumer $377.45
Excel
=AVERAGEIF(I:I,"Enterprise",K:K)File: customers-1000
What is the list price of product P0012?
Corner Bookcase, $188.81
Excel
=XLOOKUP("P0012",A:A,E:E), or =VLOOKUP("P0012",A:F,5,FALSE), on the products sheetFile: store-database