Hi there
I have 2 columns of data: date in A and a product ID in B. I've been trying to figure out how to count the number of unique product ID's by date ... A pivot table doesn't do that. I did find a thread which posed a similar question but there were no answers.
I'd appreciate it if somebody can help me with this. Thanks!
Last edited by saimike; 11-01-2011 at 12:58 PM.
Hi,
3 ways shown in this example using a Pivot Table, an array formula or a VBA UDF.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Awesome Dom, thanks!
Hi saimike,
You can do an advanced filter using the unique check box first and then a Pivot Table using count. Simply break your problem into two smaller steps. See the attached with the answer.
I guess this is a 4th way that Dom didn't think of.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
MarvinP
Thanks! I prefer not to break up the problem as far as possible because I'm a lazy bum
Dom,
If I were to scale this problem up to counting unique values subject to multiple conditions that all have to be met (ie. criteria1 AND criteria2 AND ...), what would be the best way to do it?
If it's just work I'm doing and won't be sharing the workbooks about for ease I tend to use these UDF's which will do a unique count and also for 1-3 criteria:
Please Login or Register to view this content.
To be fair though all the solutions can be expanded to more criteria, the formula can get a bit lengthy though.
Dom
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks