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
Highlighted
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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies