Pivot table - calculated field using 'IF'

[Richard Buttrey]

Hi,

I can't seen to find much written about this.

In a pivot table I have field (named 'Removed'), that exists in the underlying database with a 'Y' or 'N' in each record. I need to be able to count and sum the number of 'Y's' so that I can subtotal against a third field.

The underlying database is on a SQL server elsewhere so I don't want to add a field to the original data, and I guess the calculated Field functionality is what I want. However try as I may I can't get the PT to accept it.

I've tried an =IF(Removed="Y",1,0) and =COUNTIF(Removed,"Y") all to no avail

Any ideas anyone?

Usual TIA

[no dice]

Hard to tell without an example... where is the remove field in your pivot table? That is column heading, row heading, data, etc?

[Richard Buttrey]

Hi,

Thanks for the response. It was actually a column header. But I think I've sussed the problem. PTs seem particularly sensitive. I noticed that a date column that I was trying to group against had some blank cells in the full data set. Usual sort of problem. I tested the functionality on a small subset of the on-line database, and of course had no problems, so naively assumed that the whole database was consistent, and I was tearing my hair out, (or what's left of it) trying to understand the problem. Once I sorted out the blank data everything was OK.

Although oddly PTs as i say seem sensitive. Haven't fixed the missing dates it wasn't sufficient just to try the grouping again. I first had to refresh the data then regroup.

Thanks again for your input