Spreadsheet reporting essentials

Who this is for

For readers who can enter data in cells and want to start building small operational reports.

What you will practise

• Total a category with SUMIF while retaining negative returned amounts.
• Use SUMIFS to total orders that satisfy two conditions.
• Count qualifying orders with COUNTIFS and check equality at a threshold.
• Use an exact VLOOKUP to retrieve a price and identify missing codes.
• Check results against the source rows before sharing a report.
• Calculate a weighted unit cost and distinguish it from an unweighted average.
• Count records within a month without dropping timestamps on the final day.
• Average numeric readings for one category and recognize a missing group.

Your learning path

1. SUMIF in Excel: total a category without losing refunds

   Build a category total from a community-garden expense log. Keep negative returns, change the selected category, and check a zero result against the source rows.

2. SUMIFS: total sales that match two conditions

   A sales total is only useful when it includes the right orders. Use SUMIFS to add amounts for one region and one order status, without hiding or deleting the other rows.

3. COUNTIFS: count open orders above a minimum amount

   Count qualifying orders without adding their values. This example combines an order status with a minimum amount and checks what happens exactly at the threshold.

4. VLOOKUP exact match: find a price by product code

   Look up a product code in a small price list, return its price, and make missing codes visible instead of accepting the wrong match.

5. Weighted average in Excel: calculate unit cost with SUMPRODUCT

   Three deliveries have different quantities and unit prices. Calculate the cost of one average unit, check it against the invoice totals, and handle a list with no units.

6. COUNTIFS between dates: count a full month, including timestamps

   Count open repair tickets received in March. Use an inclusive start and an exclusive next-month boundary so a ticket arriving late on the final day is included.

7. AVERAGEIF in Excel: average readings for one category

   Choose a category, average its numeric readings, and check how a missing reading differs from a recorded zero.