Archive of Mr Excel Message Board
Back to Pivot Tables in Excel archive index
Back to archive home
Counting unique items in a pivot table
Posted by Mudface on October 16, 2001 3:48 AM
I have a pivot table linked to our company's raw products sheet. Each batch (of one of around 10 different products) has a different batch number and a selection of analyses. Each batch will be analysed several times, so may have several entries. The pivot table currently lists averages of the analyses and the number of entries made for each product by month.
Is there a way to count the number of unique batch number entries, so there will be a total of the number of batches for each product, rather than an overall total of entries? I've tried using a formula found on J Walkenbach's site =SUM(IF(FREQUENCY(data,data)>0,1,0)), however this doesn't get accepted. Any help greatly appreciated.
Maybe it's being treated as text?- Aladin's solution?
Posted by Eric on October 16, 2001 4:13 AM
I found this post using the alltheweb search engine (www.alltheweb.com) and putting in "Mrexcel unique list".
http://www.mrexcel.com/wwwboard/messages/10576.html
Re: Maybe it's being treated as text?- Aladin's solution?
Posted by Mudface on October 16, 2001 5:40 AM
Thanks, Eric. It isn't being treated as text, but in either case none of the formulas work as a calculated field. If I name the range containing the batch nos and use that in the calculated field formula box, it comes up with 'References, Names and Arrays not supported in pivot table'. If I insert the Batch No field itself into the formula, I just get a row of 1's.
Re: Counting unique items in a pivot table
Posted by Mark W. on October 16, 2001 6:28 AM
Suppose you 'Batch' field (in column A) contains
{"A";"B";"B";"C";"C";"C"}. Enter a 'Unique'
formula, =1/COUNTIF(A:A,A2), into cell B2 and
copy down. Now you can use Sum of Unique in
your PivotTable to get a unique count.
Ah, thought you were calculating off of the raw spreadsheet
Posted by Eric on October 16, 2001 6:59 AM
I agree with Mark W, you're probably going to have to add calculations to the raw data before you can get the pivot table to show what you want.
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.