SOLVED

# Countifs Multiple Columns per Row

Occasional Contributor

# 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!

5 Replies

# Re: Countifs Multiple Columns per Row

Try:
=--OR(B1:D1="NO")

and you may need to hit Ctrl+Shift+Enter after keying/typing into the formula bar.

# Re: Countifs Multiple Columns per Row

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.

# Re: Countifs Multiple Columns per Row

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!

best response confirmed by Mr_Buttons (Occasional Contributor)
Solution

# Re: Countifs Multiple Columns per Row

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

# Re: Countifs Multiple Columns per Row

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")