Pivot table problem (again)

Topic

New Reply

Morning all

On my worksheet I have to columns one is on time and has the formula =IF(I11=H11,”No,””) both behave as expected and place a Yes or blank / No or blank as required.

On my Pivot table I have selected Shipment type as the row and on time and delayed as the columns with the expectation of seeing the number of on times per shipment type and the number of delays by shipment type.

The pivot seems to populate OK but is counting the results of Yes or blank to give 30,548 and the same in the delayed column.

I have changed the field settings but I get either 0 or a #error, perhaps the problem lies in the way I am getting the Yes or No in the formulas on the Master sheet

Reply | Quote

Replies

Instead of two columns, add only one column to the master sheet, with formula

=IF(I11=<H11,"Yes","No")

Add this column to the Column Area of the pivot table AND to the Data Area. You'll get columns for Yes and for No in the pivot table.

Reply | Quote

Instead of two columns, add only one column to the master sheet, with formula

=IF(I11=<H11,"Yes","No")

Add this column to the Column Area of the pivot table AND to the Data Area. You'll get columns for Yes and for No in the pivot table.

Magic, thanks Hans

Funnily enough I originally had it as one column, albeit with a formula not as elegant as your, =IF(I11=H11,”No”)) but I did not realise that in a pivot you could use the same data from a column twice and that is why I went over to 2 columns. Working great now though, thanks again

Reply | Quote