Forum Discussion
Using COUNTIFS with Multiple Or and ANDs
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
I didn't test my previous formula. After testing, the result of such formula was the same as that of SergeiBaklan. 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.
- Try this:
=SUMPRODUCT(
(A:A={“Apple”,”Banana”})*
(B:B={“Sold”,”Not Sold”})*
(C:C={“Old”,”New”}))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.
