SUMIF

You are preparing a supplier confirmation for Veldhuis Installatie BV. The purchase ledger lists every invoice per supplier, and you want the total invoiced by one supplier to compare with their statement. Type the supplier name in F1 and the total appears in F2.

Formula in F2

=SUMIF(A2:A7, F1, B2:B7)

=SUMIF(A2:A7; F1; B2:B7)

Result: €7,975.00€ 7.975,00

Watch out: SUMIF compares the whole cell. A supplier booked as Smit Groothandel B.V. is not counted and no error warns you. Scan the supplier column for spelling variants before you trust the total.

=SUMIF(A2:A7, "Smit*", B2:B7)

=SUMIF(A2:A7; "Smit*"; B2:B7)

The audit plan says every purchase invoice above 25,00025.000 is tested individually. You want to know how much of the purchase list that covers. The list holds a credit note and an invoice of exactly 25,00025.000, and you can change the threshold in E1 without touching the formula.

Formula in E2

=SUMIF(B2:B8, ">"&E1)

=SUMIF(B2:B8; ">"&E1)

Result: €59,150.00€ 59.150,00

Watch out: Writing ">E1" inside one pair of quotes compares with the text E1 and returns 0 without any error. If a total of 0 surprises you, look at the quotes first.

=SUMIF(B2:B8, ">="&E1)

=SUMIF(B2:B8; ">="&E1)

=SUMIF(B2:B8, "<0")

=SUMIF(B2:B8; "<0")

You review the expense claims of the sales team of Veldhuis Installatie BV and want to know what share of the total went on taxis. Descriptions are free text: some start with Taxi, one has it in the middle. You type the keyword in E1 and the sheet gives a percentage you can compare with last year.

Formula in E2

=SUMIF(A2:A9, "*"&E1&"*", B2:B9) / SUM(B2:B9)

=SUMIF(A2:A9; "*"&E1&"*"; B2:B9) / SUM(B2:B9)

Result: 25%25%

Watch out: A keyword that is too short matches more than you mean. The keyword tax would also pick up every description that contains taxi, taxes or syntax, and the percentage rises with no sign that anything is off. Look at the matching rows before you report the figure.

=SUMIF(A2:A9, "*"&TRIM(E1)&"*", B2:B9) / SUM(B2:B9)

=SUMIF(A2:A9; "*"&TRIM(E1)&"*"; B2:B9) / SUM(B2:B9)

=SUMIF(A2:A9, "<>*"&E1&"*", B2:B9)

=SUMIF(A2:A9; "<>*"&E1&"*"; B2:B9)

For the debtors confirmation of Veldhuis Installatie BV you picked two customers from the open items list. The partner asks what share of the total debtors balance the sample covers.

The names of the selected customers sit in E2:E4, typed by hand, and one customer appears twice. One formula should give the share, in a form the colleague who checks your work can read without tracing three helper columns.

Formula in F2

=LET(
  customers, A2:A9,
  balances, B2:B9,
  sample, UNIQUE(E2:E4),
  total, SUM(balances),
  covered, SUM(SUMIF(customers, sample, balances)),
  IF(total = 0, 0, covered / total)
)

=LET(
  customers; A2:A9;
  balances; B2:B9;
  sample; UNIQUE(E2:E4);
  total; SUM(balances);
  covered; SUM(SUMIF(customers; sample; balances));
  IF(total = 0; 0; covered / total)
)

Result: 60%60%

Watch out: The opposite mistake: take UNIQUE away and the duplicate Raman in E4 is counted twice, so the coverage reads 95% instead of 60% with no error. UNIQUE only removes exact repeats (it ignores case), so names that differ by a trailing space or a spelling are still two items: clean the list with TRIM first.

=SUMPRODUCT(ISNUMBER(MATCH(A2:A9, E2:E4, 0)) * B2:B9) / SUM(B2:B9)

=SUMPRODUCT(ISNUMBER(MATCH(A2:A9; E2:E4; 0)) * B2:B9) / SUM(B2:B9)

=SUMIF(A2:A9, E2, B2:B9)

=SUMIF(A2:A9; E2; B2:B9)