Counting In Pivot Tables (=count)

  • Thread starter Thread starter GEM
  • Start date Start date
G

GEM

I'm trying to count individual numbers in a pivot table, for example...

I have this...

A1=1
A2=1
A3=5
A4=6
A5=7

With the "=count" function I'm getting the answer "5" because there are 5
different cells with information on them, I need to get the answer "4"
because I have four different numbers. I need excel to count A1 and A2 as 1
because it's the same number.
 
Hi,

there is no count distinct option in Excel Pivot tables. You can use the
following formula in the spreadsheet and reference the pivot table
you can use the array formula:
=SUM(1/COUNTIF(A1:A5,A1:A5))
Array formulas must be entered by pressing Shift+Ctrl+Enter, rather than
just Enter.
A non-array version would be
=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))
 
Thank you very much!!

Shane Devenshire said:
Hi,

there is no count distinct option in Excel Pivot tables. You can use the
following formula in the spreadsheet and reference the pivot table
you can use the array formula:
=SUM(1/COUNTIF(A1:A5,A1:A5))
Array formulas must be entered by pressing Shift+Ctrl+Enter, rather than
just Enter.
A non-array version would be
=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire
Click to expand...
 
Back
Top