SUMIF

Read this SUMIF example

Start with the business need: Calculate sales for a selected region. Read each argument, reference, field, or control in the example before copying it into a larger model.

=SUMIF(A2:A200,"North",D2:D200)

SUMIF: what Excel is actually doing

SUMIF applies one criterion to a criteria range and adds the corresponding values from a sum range. When the two ranges are the same, the third argument may be omitted.

Your desk challenge for lesson 19

Use a criteria cell instead of hard-coded text so the regional total can be changed safely.

• Separate the measure range from the criteria ranges.
• Make every criteria range the same size.
• Reconcile detailed results to an unconditional control total.

The SUMIF mistake to catch

SUMIF is not case-sensitive and does not prove that categories are complete; reconcile category totals to an unconditional SUM.

Evidence that your SUMIF result is trustworthy

A conditional report should add back to a known total when its categories are mutually exclusive and complete.

• Explain how the result supports this task: Calculate sales for a selected region.
• Retain one known-result check and one boundary or exception check.
• Record the source, period, unit, and refresh assumption beside the output.