SOLVED

Countifs Multiple Columns per Row

%3CLINGO-SUB%20id%3D%22lingo-sub-1612706%22%20slang%3D%22en-US%22%3ECountifs%20Multiple%20Columns%20per%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612706%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20like%20to%20achieve%20a%20COUNTIFS%20Function%20on%203%20columns.%20If%20the%20criteria%20is%20NO%2C%20then%20it%20counts%20as%201.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mr_Buttons_0-1598418130256.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214743iA360167881E3984F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Mr_Buttons_0-1598418130256.png%22%20alt%3D%22Mr_Buttons_0-1598418130256.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EHow%20can%20I%20achieve%20this%20in%20Excel%3F%20Currently%20I'm%20using%20%3DCOUNTIFS%20Function%20tho%20it's%20not%20really%20working%20as%20it%20is.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612749%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20Multiple%20Columns%20per%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612749%22%20slang%3D%22en-US%22%3ETry%3A%3CBR%20%2F%3E%3D--OR(B1%3AD1%3D%22NO%22)%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20you%20may%20need%20to%20hit%20Ctrl%2BShift%2BEnter%20after%20keying%2Ftyping%20into%20the%20formula%20bar.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612775%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20Multiple%20Columns%20per%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F512867%22%20target%3D%22_blank%22%3E%40Mr_Buttons%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECOUNTIFS%20handles%202D%20ranges%20perfectly%20well%20although%20it%20is%20not%20widely%20used%20in%20this%20way.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20COUNTIFS(range%2C%20%22NO%22)%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhere%20'range'%20might%20be%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DTable1%5B%5BColumn1%5D%3A%5BColumn3%5D%5D%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20catch%20is%20that%20the%20criterion%20ranges%20must%20be%20actual%20range%20references%20and%20not%20just%20arrays%20and%2C%20where%20there%20are%20multiple%20criterion%20ranges%2C%20they%20must%20all%20be%20of%20identical%20size.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612780%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20Multiple%20Columns%20per%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612780%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%20However%2C%20I'm%20not%20sure%20if%20it's%20working%20tho%20or%20I%20could%20be%20doing%20it%20wrong.%20The%20count%20should%20be%202%20in%20the%20last%20column%20since%20there%20are%202%20%22No%22%20listed%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mr_Buttons_5-1598423647973.png%22%20style%3D%22width%3A%201556px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214752i01041D89860B217C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Mr_Buttons_5-1598423647973.png%22%20alt%3D%22Mr_Buttons_5-1598423647973.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAttached%20is%20the%20file%20for%20your%20reference.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612981%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20Multiple%20Columns%20per%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F512867%22%20target%3D%22_blank%22%3E%40Mr_Buttons%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(--(Q2%3AS2%3D%22No%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eas%20it%20is%20if%20your%20Excel%20supports%20dynamic%20arrays%2C%20otherwise%20enter%20as%20array%20formula%20or%20use%20SUMPRODUCT%20instead%20of%20SUM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1613581%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20Multiple%20Columns%20per%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1613581%22%20slang%3D%22en-US%22%3ESorry%2C%20I%20misunderstood%20the%20question.%20If%20I%20understand%20correctly%20now%2C%20then%20Sergio's%20formula%20will%20work%20for%20you.%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%2C%20as%20Peter%20stated%2C%20I%20think%20regular%20countif%20will%20do%20what%20you%20need.%3CBR%20%2F%3E%3DCOUNTIF(C1%3AE1%2C%22No%22)%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone,

I would like to achieve a COUNTIFS Function on 3 columns. If the criteria is NO, then it counts as 1.

Mr_Buttons_0-1598418130256.png


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
Try:
=--OR(B1:D1="NO")

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

@Mr_Buttons 

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.

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.

 

Mr_Buttons_5-1598423647973.png


Attached is the file for your reference.

Thanks! 


best response confirmed by Mr_Buttons (Occasional Contributor)
Solution

@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

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