Pivot Table Error: Can Count but not Sum

[btadams]

Hello Everybody!

I have a table of data that includes a calculated column (Referrals) that uses an Index/Match array formula to pull in data from another table. I've made a pivot table from the first table and I want it to sum all referrals for all people in the selected Site. So the pivot table has Site in the Filters field, Person in the Rows field, and Referrals in the Values field. If I set the Value Field Settings to Count, it counts correctly but when I change it to Sum I get a #N/A error.

The array formula in the first table is:
{=IFERROR(INDEX(referrals,MATCH(1,(B8=lastfirst)*(D8=quarter),0)),0)}

and it looks like all values in that column are numeric.


Anybody run into this?

Thanks,

Brian

 

[SpillerBD]

There is at least one occurrence of non-numeric data.

 

[btadams]

There is at least one occurrence of non-numeric data.

Right you are! One thing I didn't mention is that I also have a field Quarter in the Filters field. When I drag that over to the Columns Field I see that there are #N/A for quarters where that person had no data. I'm just wondering why that is coming up when the array formula returns a zero if there is an error.

Brian