Excel Help: Counting unique values in a pivot table

  • Thread starter Thread starter d|b
  • Start date Start date

d|b

d|b

d|b

Associate
Joined
15 May 2007
Posts
283
I have a table with the following columns:

COL A: Caller ID
COL B: Date of Call
COL C: Time of Call (24H, i.e 1-24)
COL D: Duration of Call (sec)

I have a list of about 50k calls (i.e 50k rows)

What I am trying to do it generate a graph that will have the Date on the x axis and the number of unique caller IDs on the y axis.

I have tried using pivot tables to do this but Excel but doesnt seem to account for the fact that on any given date a caller might call more than once. In fact, Excel just counts the number of "Caller IDs" per day where as I would like it to tell me how many Unique Caller ID there where per day. I hope this makes sense...
 
have you tried looking at data->filter->advanced and selecting the unique records option?

I would create another column, which merges your date column (B) and ID column (A) and filter the list using the above. If you wanted to total up the time spend (column D) you could do a sumif() on the new filtered list.
 
If you've got a method of getting the file for me I can have a look. Spend my working day in Excel but am off with illness today and at least tomorrow so need something to keep my brain ticking over.

I'm thinking DCOUNTA might do the job but it may need to be combined with another function.
 
If you actually drop the caller ID's into your row fields, date into your column then caller ID's into the data, a count in each date column will give you unique callers.
 
Malakite said:
If you actually drop the caller ID's into your row fields, date into your column then caller ID's into the data, a count in each date column will give you unique callers.
Click to expand...

I've made an example sheet to try and work this out and unless I'm doing something wrong, that doesn't work. It still says I've two instances of January 1st with caller ID 1. It needs to return a result of 1.
 
Right I think I've got it.

Column A = Caller ID
Column B = Date of Call
Column C = Time of Call
Column D = Duration of Call

Firstly, get a list of all of the dates in, say, column E. Assuming Cell E1 has January 1st, 2010, put this formula in cell F1:

=SUM(IF(FREQUENCY(IF($B$1:$B$50000=$E1,$A$1:$A$50000),$A$1:$A$50000)>0,1,0))

Then press CONTROL+SHIFT+ENTER to tell Excel this is an array formula. If you just press ENTER it will ignore the array and give the same result a pivot table or standard count formula would.

EDIT: I'm so used to posting on a forum where I'm admin that I forgot I can't merge my posts here. Oops.
 
Last edited:
Why does it need to return a result of 1? you are looking at the number of values not a sum of them.

instead of a sum, count the number of values in your jan 1st Column and thats your unique callers?

By putting the caller Id's in your row field it will automativally only show 1 instance of each value. You could count these to show how many unique values in total. Putting the dates in the columns then allows you to split ths by your dates. Putting Callers ID's into the data field will count the volume of each caller ID. So there were 2 instances of caller ID a on Jan 1st. If we then count these values instead of sum them we have what we are looking for.
 
Last edited:
Malakite said:
Why does it need to return a result of 1? you are looking at the number of values not a sum of them.

instead of a sum, count the number of values in your jan 1st Column and thats your unique callers?
Click to expand...

I had two instances of January 1st in my test sheet, both with Caller ID 1. That means only one unique Caller ID so the result the OP would want to see is 1. Your instructions if I followed them correctly were giving a result of 2 which was a simple count of the total number of calls on the day - not the number of unique Caller IDs on the day.
 
Sorry, I think I may be explaining it wrong.

yes you will have a value of 2 in the pivot table. However if you add a further formula below the pivot table counting the number of values.



e.g. =Count(a1:140) if your pivot table column for january falls in this range.

This will give you your unique callers. As even though you show a 2 in the table we are only counting it once.
 
Sweey...can I have you email address?

@Malakite This would mean I would have 50k cols right? Also I think Sweey has the right idea with the post above mine
 
You must log in or register to reply here.
Back
Top Bottom