Excel Challenge 12 : Sum Values by Status

What You Will Learn

Five invoices. Some are PAID, some are PENDING. You need to add up the total for each status separately — paid cash in B9, pending cash in B10, and the total order count in B11.

SUMIF adds values from one column when a condition in another column is met. It is like SUM with a filter:

=SUMIF(D2:D6, "PAID", C2:C6)

SUMIF checks each row in D2:D6 for "PAID". When it finds a match, it adds the corresponding value from C2:C6. The result is the total of all paid invoices: Tesla ($1,200) + Google ($800) + Netflix ($300) = $2,300.

The same formula pointed at "PENDING" gives the pending total: Apple ($4,500) + Amazon ($1,500) = $6,000.

SUMIF checks the status column and adds matching rows only.

The three summary cells:

B9: =SUMIF(D2:D6,"PAID",C2:C6) — paid total ($2,300).
B10: =SUMIF(D2:D6,"PENDING",C2:C6) — pending total ($6,000).
B11: =COUNTA(A2:A6) — total orders (5).

With these three numbers, you can see at a glance that $2,300 is secured, $6,000 is still at risk, and the average invoice is based on 5 orders.

The SUMIF function has three parts: the range to check (D2:D6 for status), the criteria ("PAID" or "PENDING"), and the range to sum (C2:C6 for amounts). The criteria text is case-insensitive, so "paid" would also match "PAID". But partial matches do not work — "PAI" would not match "PAID" unless you use wildcards.

Alternative: SUMIFS for Multiple Conditions

If you need to filter by both status and order type (e.g., paid service orders only), use SUMIFS:

=SUMIFS(C2:C6, B2:B6, "Service", D2:D6, "PAID")

SUMIFS places the sum range first, then each condition pair. Useful when one status filter is not specific enough.

Going Further: Wildcards in SUMIF

If the status labels were inconsistent ("Paid", "PAID", "paid"), you could use a wildcard:

=SUMIF(D2:D6, "*aid*", C2:C6)

The asterisks match "Paid", "PAID", "unpaid" — anything containing "aid". Useful for messy data, but best to standardize labels first.

SUMIF is case-insensitive, so "PAID", "Paid", and "paid" all work the same way. But extra spaces will break the match — "PAID " with a trailing space would not match "PAID" in the formula.