Excel OR Practice Question: Count how many customers are from Pune or Surat (cities in B2:B6) and put the…
The question
Count how many customers are from Pune or Surat (cities in B2:B6) and put the count in D2.
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | City | Count | |
| 2 | Rahul | Pune | ? | |
| 3 | Priya | Delhi | ||
| 4 | Amit | Surat | ||
| 5 | Neha | Pune | ||
| 6 | Karan | Jaipur |
The yellow cell is where your answer goes.
Show answer
Answer in D2: 3
How it works
Explanation: Adding the two tests gives an OR for each row (0 means neither, 1 or 2 means at least one). SUMPRODUCT counts the rows where the sum is above 0.
Syntax: =SUMPRODUCT(--(((B2:B6="Pune")+(B2:B6="Surat"))>0))
Rahul and Neha are from Pune, and Amit is from Surat. Priya and Karan are not. The count is 3.
Try it yourself in the live sheet
Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.
Try this question in the live sheet →OR syntax
Returns TRUE if at least one condition given is true. OR is used to build decision rules directly into a spreadsheet instead of manually judging each row. Full OR guide.
More OR practice questions
- Write Weekend if the day is Sat or Sun, otherwise Weekday. Put it in C2 to C4.
- Write Out of range if the age is below 18 or above 60, otherwise OK. Put it in C2 to C4.
- Write West if the city is Pune or Mumbai, otherwise Other. Put it in C2 to C4.
- Give a Discount if the customer is a member (Yes) or the amount is above 5000, otherwise…
- Write Fail if any of the three marks is below 35, otherwise Pass. Put it in E2 to E4.
- Write Incomplete if the name or the city is empty, otherwise OK. Put it in C2 to C4.
- A customer is VIP if the level is Gold or Platinum and the spend is 1000 or more,…
All practice questions · Excel exercises · Interview prep · Cheat sheet