The following MS Excel spreadsheet contains a list of 100 customers. Column A…
2023
The following MS Excel spreadsheet contains a list of 100 customers. Column A contains customer names, column B contains customer categories, column C contains payment categories (0 means discounted price and 1 means full price), and column D contains the price paid. The sample below shows only some rows from the list.
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 formula correctly counts all customers who are adults and receive the discounted price?
Answer: C. =COUNTIFS(B2:B101, "=Adult", C2:C101, "=0") — CONCEPTCOUNTIFS counts rows that satisfy multiple conditions. Its arguments are supplied as repeated range–criterion pairs: criteria_range1, criteria1,…
- A.
=COUNTIF(B2:B101, "=Adult", C2:C101, "=0")
- B.
=COUNTIF(B2:B101, =Adult, C2:C101, "=0")
- C.
=COUNTIFS(B2:B101, "=Adult", C2:C101, "=0")
- D.
=COUNTIFS(B2:B101, "=0", C2:C101, "=Adult")
Show answer & explanation
Correct answer: C
CONCEPT
COUNTIFS counts rows that satisfy multiple conditions. Its arguments are supplied as repeated range–criterion pairs: criteria_range1, criteria1, criteria_range2, criteria2, and so on.
Each criterion is applied to its paired range at the same row position, and the row is counted only when all paired conditions are satisfied. The paired ranges must have the same dimensions.
APPLICATION
B2:B101 contains customer categories, so it must be paired with the criterion Adult. C2:C101 contains payment categories, so it must be paired with the criterion 0 for discounted price.
The formula =COUNTIFS(B2:B101, "=Adult", C2:C101, "=0") therefore checks the two required conditions together across rows 2 to 101.
CROSS-CHECK AND CONTRAST
In the sample, Jashan has Adult in column B and 0 in column C, so that row contributes one. Dinesh has Adult and 1, while Raman has Child and 0, so those rows do not satisfy both conditions.
=COUNTIF(B2:B101, "=Adult", C2:C101, "=0") supplies two range–criterion pairs to COUNTIF, whose syntax provides only one pair.
=COUNTIF(B2:B101, =Adult, C2:C101, "=0") also supplies two pairs to COUNTIF, and the text criterion Adult is not enclosed in quotation marks.
=COUNTIFS(B2:B101, "=Adult", C2:C101, "=0") supplies the required two pairs to COUNTIFS and associates each criterion with its correct column.
=COUNTIFS(B2:B101, "=0", C2:C101, "=Adult") swaps the criteria, applying 0 to the customer-category column and Adult to the payment-category column.
Thus the required formula is =COUNTIFS(B2:B101, "=Adult", C2:C101, "=0").