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 complete list and simply shows different categories.
A | B | C | D | |
|---|---|---|---|---|
1 | Customer Name | Customer Category | Payment Category | Price |
2 | Raman | Child | 0 | 0 |
3 | Dinesh | Adult | 1 | 5 |
4 | Jashan | Adult | 0 | 2 |
: | : | : | : | : |
101 | Amit | Child | 0 | 0 |
Statement I: The formula =COUNTIFS(C2:C101, "0") counts all customers who get discounted price.
Statement II: The formula =COUNTIF(C2:C101, "=0") counts all customers who get discounted price.
In the light of the above statements, choose the correct answer from the options given below:
Answer: A. Both Statement I and Statement II are true — ConceptCOUNTIF(range, criteria) counts cells in one range that meet one criterion. COUNTIFS(criteria_range1, criteria1, ...) evaluates one or more…
- A.
Both Statement I and Statement II are true
- B.
Both Statement I and Statement II are false
- C.
Statement I is true but Statement II is false
- D.
Statement I is false but Statement II is true
Attempted by 1 students.
Show answer & explanation
Correct answer: A
Concept
COUNTIF(range, criteria) counts cells in one range that meet one criterion. COUNTIFS(criteria_range1, criteria1, ...) evaluates one or more range–criterion pairs; a single pair is valid.
For an equality test, Excel accepts a quoted value such as "0" and also an explicit quoted expression such as "=0".
Application
Statement I uses COUNTIFS with the range C2:C101 and the criterion "0". This is one complete range–criterion pair, so the function counts cells whose value is zero.
Statement II uses COUNTIF with the same range and the criterion "=0". The equality operator explicitly selects cells whose value is zero.
Because payment category 0 denotes a discounted price, both formulas count the discounted-price customers.
Cross-check
For a small column containing 0, 1, 0, each formula returns 2. The two accepted criterion forms therefore select the same rows.
Result
Both Statement I and Statement II are true.