In an MS Excel spreadsheet of 100 customers, column B is customer category, column C is payment category (0 means discounted price and 1 means full price) and column D is the price the customer pays. Which of the following formulas would indicate the total amount paid by customers who are adults but do not get a discounted price?
A=SUMIFS(D2:D101, B2:101, =Adult, C2:C101, =1)
B=SUMIFS(B2:B101, "=Adult", C2:C101, "=1", 2:D101)
C=SUM(D2:D101)
D=SUMIFS(D2:D101, B2:B101, "=Adult", C2:C101, "=1") ✓ Correct
Correct answer: (D) =SUMIFS(D2:D101, B2:B101, "=Adult", C2:C101, "=1")
Explanation
SUMIFS adds values in D2:D101 only for rows where the category is Adult and the payment code is 1.
The sum range comes first in SUMIFS, followed by each criteria range and its criterion.
Full price is coded as 1, so the criterion on column C must be =1 to exclude discounts.
A form that mixes up the ranges or omits the quotes around criteria would fail.
Plain SUM over D2:D101 would total every customer, ignoring the two conditions entirely.
SUMIFS can combine several conditions, unlike SUMIF which allows only one.
Want more like this? Create a free account to practise a full test, track your progress, and get spaced-repetition review.
Practise on Mcqkart →🎓 Book an expert →