Excel Pivot table issue

[AccessHelp123]

Hi,

I have an excel worksheet that contains the following data.
ID Feature Planned_Delivery Actual_Delivery

1 F1 1 1
2 F1 1 1
3 F2 1 2
4 F2 1 2
5 F2 2 2
6 F3 3 3
7 F3 3 3
8 F3 3 4
9 F3 4 4

I am trying to create a pivot table that displays Feature in the row labels.
In the columns I am trying to display the count of Planned delivery and count of Actual Delivery
if delivery = 1. So for this data the pivot table should look like below.

Feature Count_of_Planned_Delivery Count_of_Actual_Delivery

F1 2 2
F2 2 0
F3 0 0

How can I accomplish this? Do I need to use custom fields with a countif function? I got some error when I tried to use countif in the custom filed.

Appreciate any help.

Thanks.

 

[SkipVought]

Hi,

ALSO drag Actual Delivery into the PAGE field area.

My results (10 seconds)...
[tt]
Actual_Delivery 1
Values
Row Labels Cnt PlnDel CntActDel
F1 2 2
Grand Total 2 2
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AccessHelp123]

Skip,

I tried doing that but it shows me the same count for all fields.

F1 2 2
F2 2 2

I am looking for a result like this

F1 2 2
F2 2 0

I should get the value 0 since there are no 1's in the actual_delivery column for the feature F2..

Thanks.

 

[Livia8]

From the data of your original worksheet I don't think you'd be able to get "F2 2 0" and "F3 0 0", because in Actual Delivery F2 has 3 items - I'm getting "F2 3 3" and "F3 4 4".

Could you please double check your worksheet? Unless the figures under the delivery fields are referring to the IDs...

 

[Livia8]

AccessHelp123,

If my last statement was correct, try with this:

In the "Row" box, add "Feature"; in the "Column" box, add both "Planned_Delivery" and "Actual_Delivery", and in the "Data" box add "Count of Feature". I think this might give you the results you're looking for.

Livia

 

[AccessHelp123]

Livia8,

I only want to count the Planned_Delivery and Actual_Delivery if the value in each of these columns is 1. That is how I am getting "F2 2 0" and "F3 0 0"

Thanks.

 

[SkipVought]

oops. I sure missed this one!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Easiest to add a helper column in your data for each like this
=if(planned_delivery=1,1,0)
Then just use Sum to do the count

Gavin

 

[AccessHelp123]

Thanks Gavin. I just did that as a final resort.