Forum Discussion

Mr_Buttons's avatar
Mr_Buttons
Copper Contributor
Aug 26, 2020
Solved

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 Funct...
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 26, 2020

    Mr_Buttons 

    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

JMB17's avatar
JMB17
Bronze Contributor
Aug 26, 2020
Try:
=--OR(B1:D1="NO")

and you may need to hit Ctrl+Shift+Enter after keying/typing into the formula bar.
Mr_Buttons's avatar
Mr_Buttons
Copper Contributor
Aug 26, 2020

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! 


  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 26, 2020

    Mr_Buttons 

    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

Resources