# Badly Need Help on Excel Formula

#### blackorchids2002

##### Board Regular
Hi Masters, just to make it understandable please see below.

AWB #
7082013260 has 3 records. Each 3 records for the same way bill number has different actual weight and dimensional weights. To get the billable weight, I used the formula MAX to get the maximum weight between actual weight and dimensional weight.

In the example, you will see the billable weight for each record but I want the billable weight to be shown as one input.
 AWB# ACTUAL WEIGHT DIMENSIONAL WEIGHT BILLABLE WEIGHT 7082013260 875 800 875 7082013260 781 800 800 7082013260 850 800 850

<tbody>
</tbody>

The output would be as per below. Kindly note that the records are not sorted out so it may be placed into different lines/rows in the excel sheet.

 AWB # ACTUAL WEIGHT DIMENSIONAL WEIGHT BILLABLE WEIGHT 7082013260 875 800 2,525 7082013260 781 800 7082013260 850 800

<tbody>
</tbody>

Thanks

<tbody>
</tbody>

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Edit the ranges as required

=SUMPRODUCT(--(B4:B6>C4:C6),(B4:B6))+ SUMPRODUCT(--(B4:B6 < C4:C6),(C4:C6))

Do you need to qualify the formula with the AWB#?

Dave, I think you need to change either the first operator to >= or the second to <=.

With the AWB# criteria in F3

=SUMPRODUCT(--(A4:A10=F3),--(B4:B10 >= C4:C10),(B4:B10))+
SUMPRODUCT(--(A4:A10=F3),--(B4:B10 < C4:C10),(C4:C10))

=SUMPRODUCT(--(B4:B6>=C4:C6),(B4:B6))+ SUMPRODUCT(--(B4:B6 < C4:C6),(C4:C6))

Thanks to Shg for the change that is required to include the amount if both weights are equal.

Thanks for the reply Dave. There are cases that a waybill number have more than 3 records.

With the formula you provided, it only covered 3 records. So how to do in more than 5 records with the same waybill number.

With the AWB# criteria in F3

=SUMPRODUCT(--(A4:A10=F3),--(B4:B10 >= C4:C10),(B4:B10))+
SUMPRODUCT(--(A4:A10=F3),--(B4:B10 < C4:C10),(C4:C10))

=SUMPRODUCT(--(B4:B6>=C4:C6),(B4:B6))+ SUMPRODUCT(--(B4:B6 < C4:C6),(C4:C6))

Thanks to Shg for the change that is required to include the amount if both weights are equal.

Edit the ranges in the formula to include the full length of your data.

Thanks Dave. i works ..

I just need to understand the formula. Why do you need to put a double minus sign?

=SUMPRODUCT(--(A4:A10=F3),--(B4:B10 >= C4:C10),(B4:B10))+
SUMPRODUCT(--(A4:A10=F3),--(B4:B10 < C4:C10),(C4:C10))

Edit the ranges in the formula to include the full length of your data.

Hi Dave,

The formula calculates well and thank you for that. However, there's still a missing formula where the first record should reflect the total number as per below.

 AWB # ACTUAL WEIGHT DIMENSIONAL WEIGHT BILLABLE WEIGHT 7082013260 875 800 2,525 7082013260 781 800 7082013260 850 800

<tbody>
</tbody>

Edit the ranges in the formula to include the full length of your data.

Replies
2
Views
199
Replies
1
Views
1K
Replies
1
Views
200
Replies
0
Views
527
Replies
2
Views
567

1,196,483
Messages
6,015,460
Members
441,897
Latest member
erma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back