• 413K Members
• 7,504 Online
• 471K 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.

Highlighted

# Re: Using COUNTIFS with Multiple Or and ANDs

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

# 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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 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