In the following MS-Excel spreadsheet, you are given a list of 100 customers.…
2023
In the following MS-Excel spreadsheet, you are given a list of 100 customers. Column 'A' represents their names, 'B' is for customer category, 'C' for payment category (0 means discounted price and 1 means full price), and 'D' indicates price that customer pays. The example spreadsheet below is not the actual list and simply shows different categories.
A | B | C | D | |
|---|---|---|---|---|
1 | Customer Name | Customer Category | Payment Category | Price (Rs.) |
2 | Raman | Child | 0 | 0 |
3 | Dinesh | Adult | 1 | 5 |
4 | Jashan | Adult | 0 | 2 |
: | : | : | : | : |
101 | Amit | Child | 0 | 0 |
Which of the following formula would indicate the total amount paid by customers who are adults but do not get discounted price?
Answer: D. =SUMIFS(D2:D101, B2:B101, "=Adult", C2:C101, "=1") — CONCEPTSUMIFS adds values from a sum range only for rows that satisfy every supplied range-and-criterion pair. Its syntax is SUMIFS(sum_range,…
- A.
=SUMIFS(D2:D101, B2:B101, =Adult, C2:C101, =1)
- B.
=SUMIFS(B2:B101, "=Adult", C2:C101, "=1", D2:D101)
- C.
=SUM(D2:D101)
- D.
=SUMIFS(D2:D101, B2:B101, "=Adult", C2:C101, "=1")
Attempted by 1 students.
Show answer & explanation
Correct answer: D
CONCEPT
SUMIFS adds values from a sum range only for rows that satisfy every supplied range-and-criterion pair. Its syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...).
APPLICATION
Use D2:D101 as sum_range because column D contains the amounts to add.
Use B2:B101 with the criterion "=Adult" to retain adult-customer rows.
Use C2:C101 with the criterion "=1" because payment category 1 denotes the full, non-discounted price.
Combining those arguments gives =SUMIFS(D2:D101, B2:B101, "=Adult", C2:C101, "=1").
CROSS-CHECK
In the sample rows, Dinesh has B = Adult and C = 1, so his amount is included; Jashan has C = 0 and is excluded, while the child rows are excluded by the B criterion. Therefore the formula returns the requested total.