Forum Discussion
Solved
Data Model Pivot is counting blanks as 1
I have countif formula, that generates a 1 when one date is greater than another. However, in my data model pivot table it is counting all the blank values as well. Any way around this?
Hi,
As i understood you use formula like
=IF(date1>date2,1,"")
and count this field in pivot table. Yes, it will count everything includes "". You may apply same field as a filter to pivot table and unfilter empty values to receive correct result.
Alternatively you may use formula
=--(date1>date2)
which returns 1 or 0, apply to these cells custom format
0;0;;@
to make zeroes invisible, refresh pivot table (to shift from text field type to numeric) and use Sum instead of Count.
Something like this.
