Return number OR sum based on two criteria

[John in MN]

Does anyone know how to get an "either - or" kind of return. Here's a sample
of what I'm looking at:

Order ID boxes freight $$
999242849 4.00 11.51
999242850 3.00 113.43
999242851 1.00 3.08
999242851 1.00 20.75
999242852 1.00 16.61
999242863 4.00 32.26
999242863 4.00 32.26
999242863 4.00 32.26
999242863 4.00 32.26

On a separate worksheet, I need to create one row for each order listing
what's in column C. Each line represents a tracking number from shipping. If
one shipment has 4 boxes (like order 999242863), I only need to pull the
$32.26 through once. But on order 999242851, there were two shipments and I
need to add them together to get $23.83. Other orders have one shipment with
one tracking number, so they are easy. I need a formula that will pull these
numbers through. I have over 7 thousand lines to deal with on a monthly
basis.

Thanks!
John

 

[duane]

if I understand you, you want to bring back the total $ for orders where the
$ on each line are different, otherwise just bring back the $ on one line for
that order.

I put the data in a4:c:12, and listed a part number in cell a18. This
formula would work, unless the first line for an order with multiple $
happens to have $ that is average of all the lines for that order. Sorting
the data apprpriately would resolve that (sort by part number 1st, then by $
number second).

=IF(AVERAGE(IF($A$4:$A$12=$A18,$C$4:$C$12))=VLOOKUP($A18,$A$4:$C$12,3,FALSE),VLOOKUP($A18,$A$4:$C$12,3,FALSE),SUMPRODUCT(($A$4:$A$12=$A18)*($C$4:$C$12)))

is an array formula

 

[John in MN]

Thanks, Duane! I'll give that a shot!

if I understand you, you want to bring back the total $ for orders where the
$ on each line are different, otherwise just bring back the $ on one line for
that order.

I put the data in a4:c:12, and listed a part number in cell a18. This
formula would work, unless the first line for an order with multiple $
happens to have $ that is average of all the lines for that order. Sorting
the data apprpriately would resolve that (sort by part number 1st, then by $
number second).

=IF(AVERAGE(IF($A$4:$A$12=$A18,$C$4:$C$12))=VLOOKUP($A18,$A$4:$C$12,3,FALSE),VLOOKUP($A18,$A$4:$C$12,3,FALSE),SUMPRODUCT(($A$4:$A$12=$A18)*($C$4:$C$12)))

is an array formula