Consider an MS-Excel sheet where column A holds Product Code, column B holds State and column C holds Quality, with rows: (A1, HP, A), (A2, MP, A), (A3, UP, A), (A4, UP, B), (A5, UP, B). Which of the following Excel formulae would correctly answer 'How many product codes from UP are of Grade A quality?' Choose the correct answer from the options given below.
1.= COUNTIFS(B2:B6, "=UP", C2:C6, "=A")
2.= COUNTIFS(C2:C6, "=A", B2:B6, "=UP")
3.= COUNTIFS(B2:B6, "UP", C2:C6, "A")
A1 and 2 only
B1 and 3 only
C2 and 3 only
D1, 2 and 3 ✓ Correct
Correct answer: (D) 1, 2 and 3
Explanation
Statement 1 correctly counts UP rows of grade A, and Excel accepts the equals UP and equals A syntax as an equality match.
Statement 2 gives the same count because COUNTIFS treats its criteria pairs regardless of the order they are listed.
Statement 3 also works since plain UP and A already mean an exact match, needing no equals sign.
All three formulas return the single matching row, the UP entry graded A.
COUNTIFS counts rows meeting every condition across the paired ranges.
Each criteria range must be the same size for the function to align them.
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 →