Hi all, perhaps one of you guys can help me here...
I have a list of about 6000 phone numbers that I need to evaluate for a call
center report. These are numbers that have called the office.

I need the count of unique phone numbers that have called. That one wasn't
too difficult:
=SUM(IF(FREQUENCY(C:C,C:C)0, 1,0))

Now, the tough part. Within those 6000 numbers, I need to find the number of
unique callers PER AREA CODE. I tried that large formula that floats around
the internet for the FREQUENCY formula, but it didn't work (Sorry I don't
have it handy to paste into here).
Keep in mind that this HAS to be a formula because this data will change
quite often and somebody might not always be around to use the advanced
filter to get uniques or use the autofilter.

Let me know if you come up with anything. I would send you the spreadsheet,
but due to the fact that it has sensitive customer information in it and I
doubt they'd even want their phone information on the internet, just make one
up with a bunch of phone numbers in various area codes.

Are the area codes part of the phone number field, or are they in a seperate
column?
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Shaun M" wrote:

Hi all, perhaps one of you guys can help me here...
I have a list of about 6000 phone numbers that I need to evaluate for a call
center report. These are numbers that have called the office.

I need the count of unique phone numbers that have called. That one wasn't
too difficult:
=SUM(IF(FREQUENCY(C:C,C:C)0, 1,0))

Now, the tough part. Within those 6000 numbers, I need to find the number of
unique callers PER AREA CODE. I tried that large formula that floats around
the internet for the FREQUENCY formula, but it didn't work (Sorry I don't
have it handy to paste into here).
Keep in mind that this HAS to be a formula because this data will change
quite often and somebody might not always be around to use the advanced
filter to get uniques or use the autofilter.

Let me know if you come up with anything. I would send you the spreadsheet,
but due to the fact that it has sensitive customer information in it and I
doubt they'd even want their phone information on the internet, just make one
up with a bunch of phone numbers in various area codes.

Try this array formula** :

A1 = area code to count for

B1:B20 = area codes
C1:C20 = phone numbers

=SUM(IF(FREQUENCY(IF(B1:B20=A1,C1:C20),C1:C20)0,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.

--
Biff
Microsoft Excel MVP


"Shaun M" <Shaun wrote in message
...
Hi all, perhaps one of you guys can help me here...
I have a list of about 6000 phone numbers that I need to evaluate for a
call
center report. These are numbers that have called the office.

I need the count of unique phone numbers that have called. That one wasn't
too difficult:
=SUM(IF(FREQUENCY(C:C,C:C)0, 1,0))

Now, the tough part. Within those 6000 numbers, I need to find the number
of
unique callers PER AREA CODE. I tried that large formula that floats
around
the internet for the FREQUENCY formula, but it didn't work (Sorry I don't
have it handy to paste into here).
Keep in mind that this HAS to be a formula because this data will change
quite often and somebody might not always be around to use the advanced
filter to get uniques or use the autofilter.

Let me know if you come up with anything. I would send you the
spreadsheet,
but due to the fact that it has sensitive customer information in it and I
doubt they'd even want their phone information on the internet, just make
one
up with a bunch of phone numbers in various area codes.