• 412K Members
• 9,782 Online
• 470K 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
Highlighted

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

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