Excel MAX - Help

blackorchids2002 · May 25, 2015

Hi Masters,

I'll be needing your help again. Just to give you a quick overview, I have a file that list all our shipment orders. It has way bill number and we charge our customers based on the billable weight multiplied by the rate. The problem with the file that I was trying to consolidate, I have a way bill number that has several lines (the same waybill number but has different weights).

To get the billable weight, it is calculated by getting the " Maximum" weight between actual weight and dimensional weight. Whichever is higher that will be our billable weight.

From column A to AR is the raw data from system, and column AS to BE is where I put some formula to get dimensional weight, billable weight, transpo cost, fuel and etc.

What needs to be fixed is to get the billable weight summarize in one line for the same waybill number and it should be reflected on the first record of the same way bill number. Example, waybill # 7082013260 has 3 records (not sorted) in the file but has different weights. The first record will have the summary of billable weight and the last two records should be black. Only in the billable weight column that I need help on how to do it.

I can send the file so you will see it.

[TABLE="width: 2144"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]AWBNumber[/TD]
[TD]SenderState[/TD]
[TD]SenderPostCode[/TD]
[TD]ReceiverCity[/TD]
[TD]ReceiverState[/TD]
[TD]ReceiverPostCode[/TD]
[TD]TotalPackages[/TD]
[TD]TotalWeight[/TD]
[TD]DeclaredValue[/TD]
[TD]RouteReference1[/TD]
[TD]AccountName[/TD]
[TD]RouteCode[/TD]
[TD]DIM WEIGHT[/TD]
[TD]ACTUAL WEIGHT[/TD]
[TD]BILLABLE WEIGHT[/TD]
[TD]transportation cahrge[/TD]
[TD]fuel charge[/TD]
[TD]additinol charges[/TD]
[TD]total charge[/TD]
[TD]inv number[/TD]
[TD]Actual WGT:[/TD]
[TD]L[/TD]
[TD]W[/TD]
[TD]H[/TD]
[TD]Rate[/TD]
[TD]Dim Weight[/TD]
[TD]Billable Weight[/TD]
[TD]Transportation Cost[/TD]
[TD]Fuel Surcharge[/TD]
[TD]dec value charge [/TD]
[TD]total charge [/TD]
[/TR]
[TR]
[TD]7082026324[/TD]
[TD]CA[/TD]
[TD]90015[/TD]
[TD]MIAMI [/TD]
[TD]FL[/TD]
[TD]33127[/TD]
[TD]5[/TD]
[TD]290[/TD]
[TD]0[/TD]
[TD]1453430 AMLC[/TD]
[TD]RACHEL PALLY INC[/TD]
[TD]M-XGR[/TD]
[TD][/TD]
[TD]58[/TD]
[TD]58[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]290[/TD]
[TD]29[/TD]
[TD]17[/TD]
[TD]20[/TD]
[TD]0.69[/TD]
[TD] 229.30[/TD]
[TD] 290.00[/TD]
[TD] 200.10[/TD]
[TD] 40.02[/TD]
[TD] - [/TD]
[TD] 240.12[/TD]
[/TR]
[TR]
[TD]7082023233[/TD]
[TD]CA[/TD]
[TD]90014[/TD]
[TD]BROOKLYN[/TD]
[TD]NY[/TD]
[TD]11232[/TD]
[TD]6[/TD]
[TD]204[/TD]
[TD]0[/TD]
[TD]1455275 AMLC[/TD]
[TD]A CURRENT AFFAIR[/TD]
[TD]M-XGR[/TD]
[TD]30[/TD]
[TD]34[/TD]
[TD]34[/TD]
[TD]39.5[/TD]
[TD]7.9[/TD]
[TD]0[/TD]
[TD]47.4[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]18[/TD]
[TD]0.69[/TD]
[TD] 180.84[/TD]
[TD] 200.00[/TD]
[TD] 138.00[/TD]
[TD] 27.60[/TD]
[TD] - [/TD]
[TD] 165.60[/TD]
[/TR]
[TR]
[TD]7082023196[/TD]
[TD]CA[/TD]
[TD]90014[/TD]
[TD]BROOKLYN[/TD]
[TD]NY[/TD]
[TD]11232[/TD]
[TD]8[/TD]
[TD]190[/TD]
[TD]0[/TD]
[TD]1455275 AMLC[/TD]
[TD]A CURRENT AFFAIR[/TD]
[TD]M-XGR[/TD]
[TD]27.38889[/TD]
[TD]24[/TD]
[TD]27.38889[/TD]
[TD]39.5[/TD]
[TD]7.9[/TD]
[TD]0[/TD]
[TD]47.4[/TD]
[TD][/TD]
[TD]190[/TD]
[TD]29[/TD]
[TD]17[/TD]
[TD]12[/TD]
[TD]0.69[/TD]
[TD] 220.13[/TD]
[TD] 220.13[/TD]
[TD] 151.89[/TD]
[TD] 30.38[/TD]
[TD] - [/TD]
[TD] 182.27[/TD]
[/TR]
[TR]
[TD]7082023126[/TD]
[TD]CA[/TD]
[TD]90014[/TD]
[TD]BROOKLYN[/TD]
[TD]NY[/TD]
[TD]11232[/TD]
[TD]2[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]1455275 AMLC[/TD]
[TD]A CURRENT AFFAIR[/TD]
[TD]M-XGR[/TD]
[TD]8.888889[/TD]
[TD]18[/TD]
[TD]18[/TD]
[TD]39.5[/TD]
[TD]7.9[/TD]
[TD]0[/TD]
[TD]47.4[/TD]
[TD][/TD]
[TD]35[/TD]
[TD]16[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]0.69[/TD]
[TD] 17.86[/TD]
[TD] 35.00[/TD]
[TD] 24.15[/TD]
[TD] 4.83[/TD]
[TD] - [/TD]
[TD] 28.98[/TD]
[/TR]
[TR]
[TD]7082023071[/TD]
[TD]CA[/TD]
[TD]90014[/TD]
[TD]BROOKLYN[/TD]
[TD]NY[/TD]
[TD]11232[/TD]
[TD]2[/TD]
[TD]130[/TD]
[TD]0[/TD]
[TD]1455275 AMLC[/TD]
[TD]A CURRENT AFFAIR[/TD]
[TD]M-XGR[/TD]
[TD]45.64815[/TD]
[TD]65[/TD]
[TD]65[/TD]
[TD]39.5[/TD]
[TD]7.9[/TD]
[TD]0[/TD]
[TD]47.4[/TD]
[TD][/TD]
[TD]130[/TD]
[TD]29[/TD]
[TD]17[/TD]
[TD]20[/TD]
[TD]0.69[/TD]
[TD] 91.72[/TD]
[TD] 130.00[/TD]
[TD] 89.70[/TD]
[TD] 17.94[/TD]
[TD] - [/TD]
[TD] 107.64[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD]CA[/TD]
[TD]90404[/TD]
[TD]LAS VEGAS[/TD]
[TD]NV[/TD]
[TD]89119[/TD]
[TD]2[/TD]
[TD]1631[/TD]
[TD]0[/TD]
[TD]49583347 FA[/TD]
[TD]King Baby[/TD]
[TD]M-XGR[/TD]
[TD]1600[/TD]
[TD]1631[/TD]
[TD]1631[/TD]
[TD]881.64[/TD]
[TD]176.33[/TD]
[TD]0[/TD]
[TD]1057.97[/TD]
[TD] [/TD]
[TD]875[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD]75[/TD]
[TD]0.41[/TD]
[TD] 800.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7082022975[/TD]
[TD]CA[/TD]
[TD]90014[/TD]
[TD]BROOKLYN[/TD]
[TD]NY[/TD]
[TD]11232[/TD]
[TD]5[/TD]
[TD]260[/TD]
[TD]0[/TD]
[TD]1455275 AMLC[/TD]
[TD]A CURRENT AFFAIR[/TD]
[TD]M-XGR[/TD]
[TD]26.66667[/TD]
[TD]52[/TD]
[TD]52[/TD]
[TD]39.5[/TD]
[TD]7.9[/TD]
[TD]0[/TD]
[TD]47.4[/TD]
[TD][/TD]
[TD]260[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]16[/TD]
[TD]0.69[/TD]
[TD] 133.95[/TD]
[TD] 260.00[/TD]
[TD] 179.40[/TD]
[TD] 35.88[/TD]
[TD] - [/TD]
[TD] 215.28[/TD]
[/TR]
[TR]
[TD]7082022920[/TD]
[TD]CA[/TD]
[TD]90014[/TD]
[TD]BROOKLYN[/TD]
[TD]NY[/TD]
[TD]11232[/TD]
[TD]5[/TD]
[TD]265[/TD]
[TD]0[/TD]
[TD]1455275 AMLC[/TD]
[TD]A CURRENT AFFAIR[/TD]
[TD]M-XGR[/TD]
[TD]34.23611[/TD]
[TD]53[/TD]
[TD]53[/TD]
[TD]39.5[/TD]
[TD]7.9[/TD]
[TD]0[/TD]
[TD]47.4[/TD]
[TD][/TD]
[TD]265[/TD]
[TD]29[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]0.69[/TD]
[TD] 171.98[/TD]
[TD] 265.00[/TD]
[TD] 182.85[/TD]
[TD] 36.57[/TD]
[TD] - [/TD]
[TD] 219.42[/TD]
[/TR]
[TR]
[TD]7082022006[/TD]
[TD]GA[/TD]
[TD]30324[/TD]
[TD]LAS VEGAS[/TD]
[TD]NV[/TD]
[TD]89119[/TD]
[TD]6[/TD]
[TD]573[/TD]
[TD]0[/TD]
[TD]49584910 FA[/TD]
[TD]Kybele Sterling[/TD]
[TD]M-X3D[/TD]
[TD]45.64815[/TD]
[TD]96[/TD]
[TD]96[/TD]
[TD]105[/TD]
[TD]21[/TD]
[TD]0[/TD]
[TD]126[/TD]
[TD][/TD]
[TD]578[/TD]
[TD]30[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]0.69[/TD]
[TD] 318.14[/TD]
[TD] 578.00[/TD]
[TD] 398.82[/TD]
[TD] 79.76[/TD]
[TD] - [/TD]
[TD] 478.58[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD]CA[/TD]
[TD]90404[/TD]
[TD]LAS VEGAS[/TD]
[TD]NV[/TD]
[TD]89119[/TD]
[TD]2[/TD]
[TD]1631[/TD]
[TD]0[/TD]
[TD]49583347 FA[/TD]
[TD]King Baby[/TD]
[TD]M-XGR[/TD]
[TD]1600[/TD]
[TD]1631[/TD]
[TD]1631[/TD]
[TD]881.64[/TD]
[TD]176.33[/TD]
[TD]0[/TD]
[TD]1057.97[/TD]
[TD] [/TD]
[TD]781[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD]75[/TD]
[TD]0.41[/TD]
[TD] 800.00[/TD]
[TD] 1,725.00[/TD]
[TD] 707.25[/TD]
[TD] 134.38[/TD]
[TD] - [/TD]
[TD] 841.63[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD]CA[/TD]
[TD]90404[/TD]
[TD]LAS VEGAS[/TD]
[TD]NV[/TD]
[TD]89119[/TD]
[TD]2[/TD]
[TD]1631[/TD]
[TD]0[/TD]
[TD]49583347 FA[/TD]
[TD]King Baby[/TD]
[TD]M-XGR[/TD]
[TD]1600[/TD]
[TD]1631[/TD]
[TD]1631[/TD]
[TD]881.64[/TD]
[TD]176.33[/TD]
[TD]0[/TD]
[TD]1057.97[/TD]
[TD] [/TD]
[TD]850[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD]75[/TD]
[TD]0.41[/TD]
[TD] 800.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

gaz_chops · May 25, 2015

Can you move the "Actual WGT" column next to the "Dim Weight" column? If so then try this array entered formula (Ctrl Shift & Enter)

=IF(COUNTIF($A$2:A2,A2)=1,MAX(IF($A$2:$A$12=A2,$Y$2:$Z$12)),"")

Y2:Z12 are the Act WGT & Dim Weight columns

Code:

[TABLE="width: 520"]
<tbody>[TR]
[TD="width: 65"]Actual WGT:[/TD]
[TD="width: 65"]Dim Weight[/TD]
[TD="width: 65"]Billable Weight[/TD]
[TD="width: 65"]Transportation Cost[/TD]
[TD="width: 65"]Fuel Surcharge[/TD]
[TD="width: 65"]dec value charge[/TD]
[TD="width: 65"]total charge[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="align: right"]290[/TD]
[TD="align: right"]229.3[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]200.1[/TD]
[TD="align: right"]40.02[/TD]
[TD]-[/TD]
[TD="align: right"]240.12[/TD]
[TD="align: right"]290[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD="align: right"]180.84[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]27.6[/TD]
[TD]-[/TD]
[TD="align: right"]165.6[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]190[/TD]
[TD="align: right"]220.13[/TD]
[TD="align: right"]220.13[/TD]
[TD="align: right"]151.89[/TD]
[TD="align: right"]30.38[/TD]
[TD]-[/TD]
[TD="align: right"]182.27[/TD]
[TD="align: right"]220.13[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]17.86[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]24.15[/TD]
[TD="align: right"]4.83[/TD]
[TD]-[/TD]
[TD="align: right"]28.98[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD="align: right"]91.72[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]89.7[/TD]
[TD="align: right"]17.94[/TD]
[TD]-[/TD]
[TD="align: right"]107.64[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]875[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]875[/TD]
[/TR]
[TR]
[TD="align: right"]260[/TD]
[TD="align: right"]133.95[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]179.4[/TD]
[TD="align: right"]35.88[/TD]
[TD]-[/TD]
[TD="align: right"]215.28[/TD]
[TD="align: right"]260[/TD]
[/TR]
[TR]
[TD="align: right"]265[/TD]
[TD="align: right"]171.98[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]182.85[/TD]
[TD="align: right"]36.57[/TD]
[TD]-[/TD]
[TD="align: right"]219.42[/TD]
[TD="align: right"]265[/TD]
[/TR]
[TR]
[TD="align: right"]578[/TD]
[TD="align: right"]318.14[/TD]
[TD="align: right"]578[/TD]
[TD="align: right"]398.82[/TD]
[TD="align: right"]79.76[/TD]
[TD]-[/TD]
[TD="align: right"]478.58[/TD]
[TD="align: right"]578[/TD]
[/TR]
[TR]
[TD="align: right"]781[/TD]
[TD="align: right"]800[/TD]
[TD="class: xl63, align: right"]1,725.00[/TD]
[TD="align: right"]707.25[/TD]
[TD="align: right"]134.38[/TD]
[TD]-[/TD]
[TD="align: right"]841.63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]850[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

blackorchids2002 · May 25, 2015

Hi Masters, just to make it clearer.

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,
[TABLE="width: 500"]
<tbody>[TR]
[TD]AWB#[/TD]
[TD]ACTUAL WEIGHT[/TD]
[TD]DIMENSIONAL WEIGHT[/TD]
[TD]BILLABLE WEIGHT[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD="align: center"]875[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]875[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD="align: center"]781[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]800[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD="align: center"]850[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]850[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]AWB #[/TD]
[TD]ACTUAL WEIGHT[/TD]
[TD]DIMENSIONAL WEIGHT[/TD]
[TD]BILLABLE WEIGHT[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD]875[/TD]
[TD]800[/TD]
[TD]2,525[/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD]781[/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7082013260[/TD]
[TD]850[/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

blackorchids2002 · May 25, 2015

Hi gaz_chops,

I did that formula already. In the example it has 3 records.

gaz_chops said:

Can you move the "Actual WGT" column next to the "Dim Weight" column? If so then try this array entered formula (Ctrl Shift & Enter)

=IF(COUNTIF($A$2:A2,A2)=1,MAX(IF($A$2:$A$12=A2,$Y$2:$Z$12)),"")

Y2:Z12 are the Act WGT & Dim Weight columns

Code:

[TABLE="width: 520"]
<tbody>[TR]
[TD="width: 65"]Actual WGT:[/TD]
[TD="width: 65"]Dim Weight[/TD]
[TD="width: 65"]Billable Weight[/TD]
[TD="width: 65"]Transportation Cost[/TD]
[TD="width: 65"]Fuel Surcharge[/TD]
[TD="width: 65"]dec value charge[/TD]
[TD="width: 65"]total charge[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="align: right"]290[/TD]
[TD="align: right"]229.3[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]200.1[/TD]
[TD="align: right"]40.02[/TD]
[TD]-[/TD]
[TD="align: right"]240.12[/TD]
[TD="align: right"]290[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD="align: right"]180.84[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]27.6[/TD]
[TD]-[/TD]
[TD="align: right"]165.6[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]190[/TD]
[TD="align: right"]220.13[/TD]
[TD="align: right"]220.13[/TD]
[TD="align: right"]151.89[/TD]
[TD="align: right"]30.38[/TD]
[TD]-[/TD]
[TD="align: right"]182.27[/TD]
[TD="align: right"]220.13[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]17.86[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]24.15[/TD]
[TD="align: right"]4.83[/TD]
[TD]-[/TD]
[TD="align: right"]28.98[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD="align: right"]91.72[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]89.7[/TD]
[TD="align: right"]17.94[/TD]
[TD]-[/TD]
[TD="align: right"]107.64[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]875[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]875[/TD]
[/TR]
[TR]
[TD="align: right"]260[/TD]
[TD="align: right"]133.95[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]179.4[/TD]
[TD="align: right"]35.88[/TD]
[TD]-[/TD]
[TD="align: right"]215.28[/TD]
[TD="align: right"]260[/TD]
[/TR]
[TR]
[TD="align: right"]265[/TD]
[TD="align: right"]171.98[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]182.85[/TD]
[TD="align: right"]36.57[/TD]
[TD]-[/TD]
[TD="align: right"]219.42[/TD]
[TD="align: right"]265[/TD]
[/TR]
[TR]
[TD="align: right"]578[/TD]
[TD="align: right"]318.14[/TD]
[TD="align: right"]578[/TD]
[TD="align: right"]398.82[/TD]
[TD="align: right"]79.76[/TD]
[TD]-[/TD]
[TD="align: right"]478.58[/TD]
[TD="align: right"]578[/TD]
[/TR]
[TR]
[TD="align: right"]781[/TD]
[TD="align: right"]800[/TD]
[TD="class: xl63, align: right"]1,725.00[/TD]
[TD="align: right"]707.25[/TD]
[TD="align: right"]134.38[/TD]
[TD]-[/TD]
[TD="align: right"]841.63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]850[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

gaz_chops · May 25, 2015

I didn't realise from your original post that you wanted the Sum of the Max amounts.

Create a new column to get the Max of Act & Dim Weight, then enter

=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$12,A2,$AA$2:$AA$12),"")

Code:

[TABLE="width: 520"]
<!--StartFragment--> <colgroup><col width="65" span="8" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]Actual WGT:[/TD]
  [TD="width: 65"]L[/TD]
  [TD="width: 65"]W[/TD]
  [TD="width: 65"]H[/TD]
  [TD="width: 65"]Rate[/TD]
  [TD="width: 65"]Dim Weight[/TD]
  [TD="width: 65"]Max[/TD]
  [TD="width: 65"]Billable Weight[/TD]
 [/TR]
 [TR]
  [TD="align: right"]290[/TD]
  [TD="align: right"]29[/TD]
  [TD="align: right"]17[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]229.3[/TD]
  [TD="align: right"]290[/TD]
  [TD="align: right"]290[/TD]
 [/TR]
 [TR]
  [TD="align: right"]200[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]18[/TD]
  [TD="align: right"]18[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]180.84[/TD]
  [TD="align: right"]200[/TD]
  [TD="align: right"]200[/TD]
 [/TR]
 [TR]
  [TD="align: right"]190[/TD]
  [TD="align: right"]29[/TD]
  [TD="align: right"]17[/TD]
  [TD="align: right"]12[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]220.13[/TD]
  [TD="align: right"]220.13[/TD]
  [TD="align: right"]220.13[/TD]
 [/TR]
 [TR]
  [TD="align: right"]35[/TD]
  [TD="align: right"]16[/TD]
  [TD="align: right"]12[/TD]
  [TD="align: right"]10[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]17.86[/TD]
  [TD="align: right"]35[/TD]
  [TD="align: right"]35[/TD]
 [/TR]
 [TR]
  [TD="align: right"]130[/TD]
  [TD="align: right"]29[/TD]
  [TD="align: right"]17[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]91.72[/TD]
  [TD="align: right"]130[/TD]
  [TD="align: right"]130[/TD]
 [/TR]
 [TR]
  [TD="align: right"]875[/TD]
  [TD="align: right"]48[/TD]
  [TD="align: right"]48[/TD]
  [TD="align: right"]75[/TD]
  [TD="align: right"]0.41[/TD]
  [TD="align: right"]800[/TD]
  [TD="align: right"]875[/TD]
  [TD="align: right"]2525[/TD]
 [/TR]
 [TR]
  [TD="align: right"]260[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]18[/TD]
  [TD="align: right"]16[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]133.95[/TD]
  [TD="align: right"]260[/TD]
  [TD="align: right"]260[/TD]
 [/TR]
 [TR]
  [TD="align: right"]265[/TD]
  [TD="align: right"]29[/TD]
  [TD="align: right"]17[/TD]
  [TD="align: right"]15[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]171.98[/TD]
  [TD="align: right"]265[/TD]
  [TD="align: right"]265[/TD]
 [/TR]
 [TR]
  [TD="align: right"]578[/TD]
  [TD="align: right"]30[/TD]
  [TD="align: right"]19[/TD]
  [TD="align: right"]20[/TD]
  [TD="align: right"]0.69[/TD]
  [TD="align: right"]318.14[/TD]
  [TD="align: right"]578[/TD]
  [TD="align: right"]578[/TD]
 [/TR]
 [TR]
  [TD="align: right"]781[/TD]
  [TD="align: right"]48[/TD]
  [TD="align: right"]48[/TD]
  [TD="align: right"]75[/TD]
  [TD="align: right"]0.41[/TD]
  [TD="align: right"]800[/TD]
  [TD="align: right"]800[/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]850[/TD]
  [TD="align: right"]48[/TD]
  [TD="align: right"]48[/TD]
  [TD="align: right"]75[/TD]
  [TD="align: right"]0.41[/TD]
  [TD="align: right"]800[/TD]
  [TD="align: right"]850[/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

blackorchids2002 · May 25, 2015

Thanks for the reply. It was already in my option to add a column but other user of the file might use a wrong info.

If there's no other way to do that without adding a column, then, it won't help me.

gaz_chops said:

I didn't realise from your original post that you wanted the Sum of the Max amounts.

Create a new column to get the Max of Act & Dim Weight, then enter

=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$12,A2,$AA$2:$AA$12),"")

Code:

[TABLE="width: 520"]
<tbody>[TR]
[TD="width: 65"]Actual WGT:[/TD]
[TD="width: 65"]L[/TD]
[TD="width: 65"]W[/TD]
[TD="width: 65"]H[/TD]
[TD="width: 65"]Rate[/TD]
[TD="width: 65"]Dim Weight[/TD]
[TD="width: 65"]Max[/TD]
[TD="width: 65"]Billable Weight[/TD]
[/TR]
[TR]
[TD="align: right"]290[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]229.3[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]290[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]180.84[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]190[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]220.13[/TD]
[TD="align: right"]220.13[/TD]
[TD="align: right"]220.13[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]17.86[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]91.72[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]875[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0.41[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]875[/TD]
[TD="align: right"]2525[/TD]
[/TR]
[TR]
[TD="align: right"]260[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]133.95[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]260[/TD]
[/TR]
[TR]
[TD="align: right"]265[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]171.98[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]265[/TD]
[/TR]
[TR]
[TD="align: right"]578[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.69[/TD]
[TD="align: right"]318.14[/TD]
[TD="align: right"]578[/TD]
[TD="align: right"]578[/TD]
[/TR]
[TR]
[TD="align: right"]781[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0.41[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]850[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0.41[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]850[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

gaz_chops · May 25, 2015

Why not just hide the column!

blackorchids2002 · May 25, 2015

To answer your question, it's a billing manifest and all information are very important and as much as possible no more columns to be added and hidden.

Thanks for your help anyway. I really appreciate your time.

gaz_chops said:
Why not just hide the column!

AhoyNC · May 25, 2015

Building on gaz_chops formula try this array formula.
It must be entered with CTRL-SHIHT-ENTER. If done right Excel will put {} around the formula.

Code:

=IF(COUNTIF($A$2:A2,A2)=1,SUM(IF($A$2:$A$12=$A2,IF($U$2:$U$12>=$Z$2:$Z$12,$U$2:$U$12)))+SUM(IF($A$2:$A$12=$A2,IF($Z$2:$Z$12>$U$2:$U$12,$Z$2:$Z$12))),"")

AhoyNC · May 25, 2015

Here is a non-array formula which also builds on gaz_chops formula.

Code:

=IF(COUNTIF($A$2:A2,A2)=1,SUMPRODUCT(--($A$2:$A$12=A2),--($U$2:$U$12>=$Z$2:$Z$12),$U$2:$U$12)+SUMPRODUCT(--($A$2:$A$12=A2),--($Z$2:$Z$12>$U$2:$U$12),$Z$2:$Z$12),"")

Excel MAX - Help

blackorchids2002

Board Regular

gaz_chops

Well-known Member

blackorchids2002

Board Regular

blackorchids2002

Board Regular

gaz_chops

Well-known Member

blackorchids2002

Board Regular

gaz_chops

Well-known Member

blackorchids2002

Board Regular

AhoyNC

Well-known Member

AhoyNC

Well-known Member

Similar threads

Share this page

Excel MAX - Help

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock