Forum Discussion
Countifs Multiple Columns per Row
Hi everyone,
I would like to achieve a COUNTIFS Function on 3 columns. If the criteria is NO, then it counts as 1.
How can I achieve this in Excel? Currently I'm using =COUNTIFS Function tho it's not really working as it is.
Thanks!
You may use
=SUM(--(Q2:S2="No"))as it is if your Excel supports dynamic arrays, otherwise enter as array formula or use SUMPRODUCT instead of SUM
5 Replies
COUNTIFS handles 2D ranges perfectly well although it is not widely used in this way.
= COUNTIFS(range, "NO")
where 'range' might be
=Table1[[Column1]:[Column3]]
The catch is that the criterion ranges must be actual range references and not just arrays and, where there are multiple criterion ranges, they must all be of identical size.
- Try:
=--OR(B1:D1="NO")
and you may need to hit Ctrl+Shift+Enter after keying/typing into the formula bar.- Sorry, I misunderstood the question. If I understand correctly now, then Sergio's formula will work for you.
And, as Peter stated, I think regular countif will do what you need.
=COUNTIF(C1:E1,"No") Hi JMB17,
Thanks! However, I'm not sure if it's working tho or I could be doing it wrong. The count should be 2 in the last column since there are 2 "No" listed there.
Attached is the file for your reference.
Thanks!You may use
=SUM(--(Q2:S2="No"))as it is if your Excel supports dynamic arrays, otherwise enter as array formula or use SUMPRODUCT instead of SUM
