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.

Posted by Eric on October 16, 2001 4:13 AM

Maybe it's being treated as text?- Aladin's solution?

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

Posted by Mudface on October 16, 2001 5:40 AM

Re: Maybe it's being treated as text?- Aladin's solution?

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.

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.

Posted by Eric on October 16, 2001 6:59 AM

Ah, thought you were calculating off of the raw spreadsheet

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.