Need help on doing COUNT or COUNTA on Pivot table

Hi All,

Need your help. Do you know how to do a count (or counta) from a pivot table.

Meaning - say we have a Pivot table, and we want to know for a particular column, how many rows are having a value (i.e. not null)?

Note that i need to have the range somewhat 'dynamic', hence i cant use a range. Rather i need to use something like a getpivotdata function.

I tried the below formula, but it seems, the COUNT is always returning '1'.

Help please...



=COUNTA(GETPIVOTDATA(("Period Ending"),!$A$3,"Project Status","Initiating")) --> Always returns '1'
=COUNTA(B79:B101) --> Returns correct answer.

You could do it within the pivot table itself... It adds a column but will be dynamic. Just add a calculated field with a formula to evaluate whether the cell has a valid value in it. For some reason <>"" doesn't work so I used: =IF(OR(NOT(Value<=0),NOT(Value>=0)),1,0)

'value' is any field you want to check for nulls. Then use a GetPivot function to return the total. Hope this helps!

Hi ChrisRoss,

I tried that, but can you tell me how to get the totals? Currently the column is correctly showing null values as 0, and non null values as 1; which is correct. however the grand total is also 1. And also when i did a GetPivotData, it's returned as 1 also. How do we get the totalled value of this column?

Thanks.