Jul 17 2019 10:08 AM - edited Jul 17 2019 10:22 AM
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!
Jul 17 2019 10:42 AM
Jul 17 2019 12:00 PM
Jul 17 2019 12:35 PM
Jul 17 2019 01:21 PM
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.
Jul 18 2019 02:26 AM
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.