Home

Using COUNTIFS with Multiple Or and ANDs

%3CLINGO-SUB%20id%3D%22lingo-sub-759485%22%20slang%3D%22en-US%22%3EUsing%20COUNTIFS%20with%20Multiple%20Or%20and%20ANDs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759485%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20Looking%20to%20use%20Countifs%20or%20countif%20to%20solve%20my%20problem.%20Ex.%3C%2FP%3E%3CP%3EI%20want%20to%20count%20from%20the%20columns%20A%20contains%20(Apple%2C%20Banana)%2C%20B%20contains%20(Sold%2C%20Not%20Sold)%2C%20C%20contains%20(Old%2C%20new).%20So%20I%20want%20to%20count%20it%20like%20this%20-%26nbsp%3B%20(Apple%20or%20Banana)%20AND%20(Sold%20or%20Not%20Sold)%20AND%20(Old%20or%20New)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20using%20different%20ways%20of%20countifs%2C%20but%20the%20numbers%20haven't%20come%20out%20accurate%20%3A(%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20amazing!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-759485%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759580%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20COUNTIFS%20with%20Multiple%20Or%20and%20ANDs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365347%22%20target%3D%22_blank%22%3E%40bangerz253%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20use%20COUNTIFS%20when%3C%2FP%3E%0A%3CPRE%3E%3DSUM(COUNTIFS(A%3AA%2C%7B%22Apple%22%2C%22Banana%22%7D%2CB%3AB%2C%7B%22Sold%22%2C%22Not%20Sold%22%7D%2CC%3AC%2C%7B%22Old%22%2C%22New%22%7D))%3C%2FPRE%3E%0A%3CP%3Ebut%20it%20gives%20exactly%20the%20same%20result%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bformula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759562%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20COUNTIFS%20with%20Multiple%20Or%20and%20ANDs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759562%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file%20so%20I%20could%20test%20the%20formula%20therein.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759546%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20COUNTIFS%20with%20Multiple%20Or%20and%20ANDs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759546%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BI'm%20not%20sure%20why%2C%20but%20my%20numbers%20still%20do%20not%20come%20out%20accurately.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759501%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20COUNTIFS%20with%20Multiple%20Or%20and%20ANDs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759501%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DSUMPRODUCT(%3CBR%20%2F%3E(A%3AA%3D%7B%E2%80%9CApple%E2%80%9D%2C%E2%80%9DBanana%E2%80%9D%7D)*%3CBR%20%2F%3E(B%3AB%3D%7B%E2%80%9CSold%E2%80%9D%2C%E2%80%9DNot%20Sold%E2%80%9D%7D)*%3CBR%20%2F%3E(C%3AC%3D%7B%E2%80%9COld%E2%80%9D%2C%E2%80%9DNew%E2%80%9D%7D))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-762143%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20COUNTIFS%20with%20Multiple%20Or%20and%20ANDs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-762143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365347%22%20target%3D%22_blank%22%3E%40bangerz253%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20test%20my%20previous%20formula.%20After%20testing%2C%20the%20result%20of%20such%20formula%20was%20the%20same%20as%20that%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%20Unfortunately%2C%20both%20formulas%20returned%20the%20incorrect%20result.%26nbsp%3B%3C%2FP%3E%3CP%3EThus%2C%20the%20correct%20formula%20is%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT(%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EISNUMBER(MATCH(A%3AA%2C%7B%22Apple%22%2C%22Banana%22%7D%2C0))*%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EISNUMBER(MATCH(B%3AB%2C%7B%22Sold%22%2C%22Not%20Sold%22%7D%2C0))*%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EISNUMBER(MATCH(C%3AC%2C%7B%22Old%22%2C%22New%22%7D%2C0)))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20the%20proof%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
bangerz253
Occasional Contributor

Hello, Looking to use Countifs or countif to solve my problem. Ex.

I want to count from the columns A contains (Apple, Banana), B contains (Sold, Not Sold), C contains (Old, new). So I want to count it like this -  (Apple or Banana) AND (Sold or Not Sold) AND (Old or New)

 

I've been using different ways of countifs, but the numbers haven't come out accurate :(

 

Any help would be amazing!

5 Replies
Try this:
=SUMPRODUCT(
(A:A={“Apple”,”Banana”})*
(B:B={“Sold”,”Not Sold”})*
(C:C={“Old”,”New”}))

@Twifoo I'm not sure why, but my numbers still do not come out accurately.

 

 

Please attach your sample file so I could test the formula therein.

@bangerz253 

If use COUNTIFS when

=SUM(COUNTIFS(A:A,{"Apple","Banana"},B:B,{"Sold","Not Sold"},C:C,{"Old","New"}))

but it gives exactly the same result as @Twifoo formula.

@bangerz253 

I didn't test my previous formula. After testing, the result of such formula was the same as that of @Sergei Baklan. Unfortunately, both formulas returned the incorrect result. 

Thus, the correct formula is this: 

=SUMPRODUCT(
ISNUMBER(MATCH(A:A,{"Apple","Banana"},0))*
ISNUMBER(MATCH(B:B,{"Sold","Not Sold"},0))*
ISNUMBER(MATCH(C:C,{"Old","New"},0)))

See the proof in the attached file.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies