Thread: Pivot table - calculated field using 'IF'

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

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

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