Lookups, conditional totals and checking spreadsheet data

Skills you'll practise

• Write SUMIF/SUMIFS and COUNTIF formulas with arguments in the right order
• Use XLOOKUP and explain what #N/A means and how to investigate it
• Clean common data problems: trailing spaces, numbers stored as text and duplicates
• Set up data validation to prevent invalid entries
• Reconcile a spreadsheet report to a control total from the source system

Course outline

1. 1.Lookups, conditional totals and checking spreadsheet dataVideo · 2 min
2. 2.Conditional totals and countsLesson · 16 min
3. 3.Lookups and #N/ALesson · 16 min
4. 4.Duplicates, validation and control totalsLesson · 17 min
5. 5.Lookups, conditional totals and checking spreadsheet data: knowledge checkKnowledge check · 15 questions
6. 6.The visitor report that doesn't add upScenario
7. 7.Final exam8 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 (argument order differs from SUMIF)
• Microsoft Support: COUNTIF function (spaces, non-printing characters and long strings can give unexpected results)
• Microsoft Support: XLOOKUP function
• Microsoft Support: Filter for unique values or remove duplicate values
• Microsoft Support: Apply data validation to cells
• General office administration practice (scheduling, records, written communication, workflow coordination)