SUMIFS and COUNTIFS for operational reports
For coordinators, administrators, team leads and finance staff who build weekly or monthly reports from system extracts: units by depot, tickets by status, sales by product family. Prerequisite: 'Lookups, conditional totals and checking spreadsheet data', which introduces SUMIF and COUNTIF. This course goes further on report building: argument order and range sizes in SUMIFS, COUNTIFS and AVERAGEIFS, date-range criteria driven by period cells, wildcards and exclusions, report grids that fill correctly across and down, and proving the grid against a control total. For PivotTable reports and cross-checks, see 'Excel PivotTables: build, filter, group and verify totals'; for differences between two lists, see 'Spreadsheet reconciliation: matching two lists and explaining the differences'. Based on Microsoft Support's function documentation. Your organisation's reporting calendar and code lists apply.
- Level
- Intermediate
- Length
- About 100 minutes
- Contents
- 5 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Write SUMIFS, COUNTIFS and AVERAGEIFS formulas with arguments in the right order and ranges of equal size
- Build date-range criteria from period cells with comparison operators so a report updates when the period changes
- Use wildcards, not-equal and blank criteria to match text such as product families, exclusions and missing values
- Lay out a report grid with criteria in labelled cells and references that fill correctly across and down
- Check a conditional-total report against a control total and explain rows it misses or counts twice
Course outline
- 1.Writing SUMIFS, COUNTIFS and AVERAGEIFS with the right argument orderLesson · 16 min
- 2.Building date-range criteria from period cellsLesson · 16 min
- 3.Matching text with wildcards, not-equal and blank criteriaLesson · 16 min
- 4.Laying out a report grid that fills across and downLesson · 16 min
- 5.Checking report totals against a control totalLesson · 14 min
- 6.SUMIFS and COUNTIFS for operational reports: knowledge checkKnowledge check · 14 questions
- 7.SUMIFS and COUNTIFS for operational reports: practical exerciseKnowledge check · 1 question
- 8.Final exam10 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Support: SUMIFS function
- Microsoft Support: COUNTIFS function
- Microsoft Support: AVERAGEIFS function
- Microsoft Support: SUMIF function
- Microsoft Support: Use the COUNTIF function in Microsoft Excel
- Microsoft Support: Sum values based on multiple conditions
- Microsoft Support: Using wildcard characters in searches
- Microsoft Support: EOMONTH function
- Microsoft Support: NOW function (dates as serial numbers; time as the decimal part)
- Microsoft Support: Switch between relative, absolute, and mixed references
- Microsoft Support: Using structured references with Excel tables
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.