Count Unique Items in PivotTable?

**EnergyEngineer** · 08-28-2008, 04:03 PM

Hi hi,

Do you know how to count unique items in PivotTable? Currently when I do a count it adds up all the items not the unique items.

For Example

ItemA
ItemA
ItemA
ItemB

Should result in 2 unique items but instead I get 4 items.

I am sure there is something I can do in calculated field but I just can't think of it. Any ideas?

Thanks,
~RUTH~

**Kuskush** · 08-28-2008, 06:10 PM

You don't need a Pivot table for that. To count the items in range A1:A100 use the formula below:

Please Login or Register  to view this content.

this is a matrix formula. Therefore close it by Ctrl-Shift-Enter.

**EnergyEngineer** · 08-28-2008, 10:58 PM

I haven't tried this out yet b/c I am not at the office but I am not sure I understand what this code is doing. I know the count if function and if and sum. What is the 1/ about?

Thanks,
~RUTH~

**VBA Noob** · 08-29-2008, 02:26 AM

Here another option without the array formula

=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

http://www.contextures.com/xlPivot07.html#Unique

VBA Noob

**EnergyEngineer** · 08-29-2008, 09:50 AM

Hi VBA Noob,

Can I insert this formula as a calculated field instead of adding it to the database? It is complicated since the original database is in Access so I could combine two spreadsheets. I didn't know how to create a relationship in Excel. Easy in Access. So I can't add this formula in the database of Access I don't think. Or can I?

Also how does this formula work? I have used sumproducts and ifs before but it just isn't clicking in my head.

Thanks,
~RUTH~
P.S. Seems like it has been a while. Glad you are still here! :-)

**EnergyEngineer** · 08-29-2008, 12:00 PM

VBA Noob,

I really like your clever locking of cells. I was just looking at the formula and disregarding the $. Well done!

I incorporated it into one of my original spreadsheets and it works great! Thanks again for being incredible.

~RUTH~