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

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

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.

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.

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.

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

Thanks @TonyValko that did the trick!

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

You're welcome. Thanks for the feedback!

#### Thread Information

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1