count the number of instances Blank ( may be hidden characte

[jbesr1230]

I am making a pivot table from source data that includes some kind of hidden characters, (appears to be spaces to me, but I do not know how to confirm this) for data elements with no value. The data source is an access query. I am trying to count the number of instances where there is a value in the data element using a calculated field in the pivot table. I have tried “if(isnumber(data element),1,0)” and then sum this field but that doesn’t work in the pivot table. I can add this formula to the source data, but I would rather let the pivot table do the work. So, how do I get a count of the “no data” or conversely, “is data” cells.

Thanks.

 

[Oaktree]

Try:

=--(len(substitute(a1," ",""))>0)

And sum that column...