Pivot table not recognizing formula values

[Domroy]

Hi all,

So, I have a large table ("Table 1") with a lot of data. In order to find out which region is sending me data with errors, I have some "if" formulas (eg: =IF(ISTEXT([@[Date of Referral ]])=FALSE,"","1")

I'm giving it a value of 1 if the "Date of Referral" field is not a date. I have values showing up properly, but when I go to make a pivot table to show me what regions are making errors in which fields, it gives me all zeros when I do "Sum" as the pivot table values.

Thoughts?

 

[SpillerBD]

Because you have blanks, and your value "1" is a text string.

=IF(ISTEXT([@[Date of Referral ]])=FALSE,[COLOR=#0000ff]0,1[/COLOR])

 

[Norie]

"1" is text, try removing the "" and replace "" with 0.

 

[Domroy]

Awesome! Thank you! I had zero in there before, but it didn't work, because I had quotes. I thought to give it a value, it had to have quotes?