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

Hello

Count of unique phone numbers.xlsx

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?

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

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.

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

Hello
eh308701

my formula given is array

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

You're welcome from us.