Excel MAX - Help

[blackorchids2002]

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.

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

<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>
</tbody>

Thanks in advance.

 

[gaz_chops]

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

[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]

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,

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 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

 

[blackorchids2002]

Hi gaz_chops,

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

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

[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]

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),"")

[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]

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.

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),"")

[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]

Why not just hide the column!

 

[blackorchids2002]

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.

Why not just hide the column!

 

[AhoyNC]

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.

=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]

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

=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),"")