-2

I want to have a count of count in a pivot table, anyone has ever done that ?

In the example below I want to have 1s in the column "Sum of Unique verification" when there is a value in the second column "Count of Verification".

Only constraint : I want to avoid modifying the table itself.

Thanks !

enter image description here

AlexandreG
  • 1,633
  • 2
  • 14
  • 18
  • Do you have Power Pivot available? – Rory Dec 17 '18 at 13:41
  • Are you just looking for a [distinct count](https://stackoverflow.com/a/21552440/1011724)? – Dan Dec 17 '18 at 13:41
  • @Rory I would rather avoid using external tools, pretty sure there's a way to do this with excel pivot tables – AlexandreG Dec 18 '18 at 10:18
  • @Dan unfortunately the data in the table I am basing my "Count of verification" is only "OK" or no value. So it's not the distinct count I am looking for. I could type the row label value in that column and use that distinct count but that would make the original table unreadable. – AlexandreG Dec 18 '18 at 10:20
  • @AlexandreG so then `=COUNT(PivotTableName[Count of Verification])`? – Dan Dec 18 '18 at 10:24
  • There is, but not without modifying the original table. – Rory Dec 18 '18 at 17:15
  • @Dan yes that would get me the information I need while enjoying the respecting the dynamic character of the pivottable, but apparently that formula does not work – AlexandreG Dec 19 '18 at 12:48
  • @AlexandreG can you paste here the exact formula you used? Also, the name of the pivot table and the name of the column (you might need the accent on the e for example) – Dan Dec 19 '18 at 13:03

1 Answers1

0

Can you not just stick a formula in the original table to show them???. I.e if the value you want is in B1 in C1 have =IF(B1>0,1,""). This would return the value, just include the data in the pivot chart.

This assumes that you already have this column as you have it on your pivot data, so it would just populate what you already have.