Return number OR sum based on two criteria

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

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" wrote:

> 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

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

"duane" wrote:

> 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" wrote:
>
> > 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