Special Calculated field for PivotTable

[Guest]

A part of my report needs to make use of the following three fields

Order
Delivery Number
Available (boolean field Y/N)

for e.g.
Order Delivery No. Available
1 1 Y
1 2 N
2 3 Y
3 4 Y
3 5 Y


I need to computer Order and Delivery Availability. To compute Delivery
Availability is easy. Any delivery that has Available = Y counts. Therefore
in the above example. delivery availability is 4.
Order is more complicated and this is why I need you help.
An order has many deliveries. I need excel to scan all the deliveries in
each Order and apply a Y ONLY if ALL deliveries are available.

Order Delivery No. Available Order Available
1 1 Y N
1 2 N
2 3 Y Y
3 4 Y Y
3 5 Y

Order 1 is not available because although delivery 1 is a available,
delivery 2 is not.
Order 2 is straight forward, Order 3 is available becase both delivery 4 and
5 are available.

How would I code the Order Available field using Excel formula's?

 

[Dave Peterson]

I put this in D2 and copied down:

=IF(COUNTIF($A$1:A2,A2)<>1,"",
IF(SUMPRODUCT(--($A$1:$A$100=A1),--($C$1:$C$100="y"))
=COUNTIF($A$1:$A$100,A2),"y","n"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html