# count the number of unique values given certain conditions

1. ## count the number of unique values given certain conditions

I've searched through the forums to see if my question was already out there and i feel that so many posts were so close but i couldn't wrap my head around how to make them work to my situation. I've never used =FREQUENCY or =MATCH formulas so i have no idea how to manipulate them for my needs. (i saw these being used in similar posts).

here's my specific problem. i need to count the number of unique phone numbers for outbound calls in August by each person. so i have 4 columns of information, 3 columns of criteria, and the 4th column is the list of phone numbers.

i've attached my sample data with the results i'm looking for and an explanation of the desired results. i hope someone out there gets what i'm trying to do. i hope i was clear enough! fingers crossed that there's a solution.

Erin

2. ## Re: count the number of unique values given certain conditions

Hello

Count of unique phone numbers.xlsx

3. ## Re: count the number of unique values given certain conditions

how do you tell which calls angela made? in your sample file, you have 11 distinct numbers, i used a pivot table, and it extracted 12 unique numbers, but i cant tell how to allocate which numbers to who?

4. ## Re: count the number of unique values given certain conditions

If you always have valid numbers in 'Number' column, then you can shorten the formula to;

``Please Login or Register  to view this content.``
F2 = Name, ie Angela
G1 = Month, ie August

5. ## Re: count the number of unique values given certain conditions

Thanks for the replies!

FDibbins, a pivot table won't help me. if you want to see who called which numbers, you'll have to expand the data range to include column A through D then drag the Name field to the row labels and finally filter by august and outbound.

Haseeb A and vlady, i tried both of your formulas and i'm getting some errors.

SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$100="Angela",IF(\$B\$2:\$B\$100="August",IF(\$C\$2:\$C\$100="Outbound",MATCH("~"&\$D\$2:\$D\$100,\$D\$2:\$D\$100&"",0)))),ROW(\$D\$2:\$D\$100)-ROW(D2)+1),1)) this returns an error, #value!

SUM(IF(FREQUENCY(IF((A\$2:A\$61="Angela")*(B\$2:B\$61="August")*(C\$2:C\$61="Outbound"),D\$2:D\$61),D\$2:D\$61),1)) the results is 12 and that's the count of unique phone numbers in the whole data set regardless of the criteria. also, the formula seems to be dependent on how the data is sorted. if i'm using the formula for angela and angela is listed first, then it'll return 12, but if not then it returns the error, #value!. i can't have the data dependent on how its sorted.

6. ## Re: count the number of unique values given certain conditions

You must hit CTRL+SHIFT+ENTER, NOT just ENTER, if you done successfully, you can see formula surrounded by {}

7. ## Re: count the number of unique values given certain conditions

Hello
eh308701

my formula given is array

press ctrl+shift + enter -----not just enter

8. ## Re: count the number of unique values given certain conditions

thanks you two!!! both formulas work!

i've never had to use the Ctrl+Shift+Enter with the {} so that's why i was confused. Fancy stuff! haha

9. ## Re: count the number of unique values given certain conditions

You're welcome from us.

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