Using COUNTIFS with Multiple Or and ANDs

Copper 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.