• 509K Members
• 8,490 Online
• 606K Conversations

## Using COUNTIFS with Multiple Or and ANDs

Occasional Contributor

# 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

# Re: Using COUNTIFS with Multiple Or and ANDs

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

# Re: Using COUNTIFS with Multiple Or and ANDs

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

# Re: Using COUNTIFS with Multiple Or and ANDs

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

# Re: Using COUNTIFS with Multiple Or and ANDs

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.

# Re: Using COUNTIFS with Multiple Or and ANDs

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
COUNTIF not working
sme527 in Excel on
10 Replies
Totaling using two sets of data
dazedandconfused in Excel on
2 Replies