SUMIF, COUNTIF, and AVERAGEIF

Plain SUM adds everything. Finance needs "total Science spend" or "how many quarters exceeded £15,000" without manual filtering.

James Okonkwo filtered the Budgets sheet by hand each month. SUMIF and COUNTIF replaced that with one formula each — ready for the governor's slide deck.

Open NorthgateBudget.xlsx. You will write conditional formulas on Budgets and cross-check against the Expenses log.

=SUMIF(range, criteria, [sum_range])

SUMIF sums values where a condition is true. Text criteria need quotes — "Science".

To sum Science Q1 only, put Science in A2:A6 and Q1 amounts in B2:B6:

=SUMIF(A2:A6,"Science",B2:B6) → 18000

On Budgets, click M1 and type Science Q1 total. In M2, type =SUMIF(A2:A6,"Science",B2:B6). Press Enter. Result should be 18000.

Change criteria to "Mathematics" and sum_range C2:C6 for Q2 Mathematics.

Sum every quarter column for Science without four separate formulas: =SUM(B4:E4) for Science row, or =SUMIF(A2:A6,"Science",F2:F6) if F holds annual totals.

COUNTIF counts cells matching a condition.

=COUNTIF(B2:E6,">15000") counts cells in the quarterly block strictly above 15000. Science Q4 (19000) and other high quarters increment the count.

In N1, type Quarters > 15000. In N2, type =COUNTIF(B2:E6,">15000"). Press Enter.

Try =COUNTIF(A2:A6,"Science") on the Expenses department column after you enter the log — returns 2 expense rows for Science.

If you write =SUMIF(A2:A6,Science,B2:B6) without quotes, Excel looks for a named range called Science and may return 0 or #NAME?. Wrap text criteria in quotes: "Science". Or point at a cell: =SUMIF(A2:A6,H2,B2:B6) where H2 holds Science.

AVERAGEIF averages values where a condition is true.

=AVERAGEIF(A2:A6,"Science",B2:E6) is invalid — average_range must be one column or same shape as range.

Correct pattern for average Q1 budget of departments over £12,000:

=AVERAGEIF(B2:B6,">12000",B2:B6)

Or average annual total for "Review" departments once Status column exists:

=AVERAGEIF(K2:K6,"Review",F2:F6)