# How to count unique values using multiple criterias

1. ## How to count unique values using multiple criterias

Hello,

I've tried various formulas to count the number of unique values, but I haven't found one that accounted for my second criteria. What I'm trying to do is to count the number of unique Type #'s but only for the ones that will be included. I hope my request makes sense, but if not just let me know. Thanks.  Register To Reply

2. ## Re: How to count unique values using multiple criterias

do you want to count the types - but only when yes

i have put all the types in column D (you can do this with an array formula if required)
and then used
=COUNTIFS(\$A\$2:\$A\$19,D2,\$B\$2:\$B\$19,"yes")
in E2

see attached  Register To Reply

3. ## Re: How to count unique values using multiple criterias

Try this array formula**:

=SUM(IF(FREQUENCY(IF(B2:B19="Yes",MATCH(A2:A19,A2:A19,0)),ROW(A2:A19)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.  Register To Reply

4. ## Re: How to count unique values using multiple criterias

Thank you bot @etaf and @TonyValko.

@etaf I'm sorry I guess I wasn't uber clear. I meant to ask for a formula that I could calculate the total frequency in one cell as upposed to one cell for each Type #.

@TonyValko your formula worked perfectly. Would it be possible to add one more criteria? I was hoping to have my array be a column, which means there may be blank cells. What I'd like is that if there are any blank cells it would just be ignored and not counted.  Register To Reply

5. ## Re: How to count unique values using multiple criterias

To account for empty cells in column A:

=SUM(IF(FREQUENCY(IF(B2:B19="Yes",IF(A2:A19<>"",MATCH(A2:A19,A2:A19,0))),ROW(A2:A19)-ROW(A2)+1),1))

Still array entered.  Register To Reply

6. ## Re: How to count unique values using multiple criterias

Thanks @TonyValko that did the trick!  Register To Reply

7. ## Re: How to count unique values using multiple criterias

You're welcome. Thanks for the feedback!   Register To Reply

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