# Adding multiple criteria to COUNTIFS (don't count duplicates)

1. ## Adding multiple criteria to COUNTIFS (don't count duplicates)

Hello, I'm looking for an explanation about how to add the second criteria to a COUNTIFS formula.

I have an easy sample sheet attached, but I'd really like to gain an understanding of the principle, not only a solution to today's dilemma. The main premise is that I want to count the number of the businesses that are deemed "active" in column D, but some of these businesses are listed more than once in column A. I want to exclude these duplicates from the tally. (I understand that I could simply remove duplicate rows from column A, then do a regular COUNTIF on column D, but the real sheet includes additional data that must be salvaged).

Any pointers on the failed attempted formulas in the sheet would be appreciated.

Thank you,

2. ## Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

I would think the UNIQUE function comes into play .... but as don't have 365 .....

Formula:

4. ## Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

=ROWS(UNIQUE(FILTER(A2:A14,D2:D14="Active")))

5. ## Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

All in one go

Formula:

6. ## Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

Try.
In K2

=SUMPRODUCT(1*(UNIQUE(A2:A14)<>""))

In K3

=SUMPRODUCT(1*(UNIQUE(FILTER(A2:A14,D2:D14="Active"))<>""))

In K4

=SUMPRODUCT(1*(UNIQUE(FILTER(A2:A14,D2:D14="Inactive"))<>""))

7. ## Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

Thanks, everyone. Lots of solutions do the trick here. I spent some time studying the UNIQUE formula. It was my first time using it.

8. ## Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

Glad to help & thanks for the feedback

9. ## Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

Same here.

There are currently 1 users browsing this thread. (0 members and 1 guests)