SUM and SUBTOTAL unique product item list

[MYUSERNAMEIS]

Hello,
I want to get a sum of prices for column AA, but only counting 1 occurrence of each individually named Item in column U - for example if the formula worked it would return £16 in AA42 - it would ignore duplicate Supplier Item Names (Row U)
There are some filtered out rows which I do not want to be counted.
Cells in columns X and ZZ contain formula that refer to other cells elsewhere in the spreadsheet.
Any help would be greatly appreciated.

I have previously had great help from this forum, the help I got last year was perfect - but my spreadsheet has evolved a lot since then, and the formula is not suited to what I want it to do now, I have tried tweaking it, but as an excel novice I don't know what I am doing and am getting nonsensical results.

Here is the previous thread;
https://www.mrexcel.com/forum/excel-...ered-list.html

[TABLE="class: grid, width: 948, align: left"]
<tbody>[TR]
[TD]Column[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]Row No.[/TD]
[TD]SUPPLIER[/TD]
[TD]SUPPLIER Item Name[/TD]
[TD]SUPPLIER Item Code[/TD]
[TD]SUPPLIER price / pack[/TD]
[TD]NO. to ORDER[/TD]
[TD]P+P[/TD]
[TD]Cost / Supplier Unit Ex VAT[/TD]
[TD]TOTAL ex VAT[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]amazon[/TD]
[TD]4 Digit Combination Padlock[/TD]
[TD]Desired tools[/TD]
[TD]£1.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£1.00[/TD]
[TD="align: right"]£2.00[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Ebay[/TD]
[TD]1 minute sand egg timer[/TD]
[TD]n/a[/TD]
[TD]£2.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£2.00[/TD]
[TD="align: right"]£2.00[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Ebay[/TD]
[TD]1 minute sand egg timer[/TD]
[TD]n/a[/TD]
[TD]£2.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£2.00[/TD]
[TD="align: right"]£2.00[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Amazon[/TD]
[TD]100 disposable 7" white Plastic Plates[/TD]
[TD]Best Values[/TD]
[TD]£3.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]£3.00[/TD]
[TD="align: right"]£3.00[/TD]
[TD="align: right"]£3.00[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]Amazon[/TD]
[TD]100 disposable 7" white Plastic Plates[/TD]
[TD]Best Values[/TD]
[TD]£3.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]£3.00[/TD]
[TD="align: right"]£3.00[/TD]
[TD="align: right"]£3.00[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]aladdin[/TD]
[TD]100 x skewers in bamboo 250mm[/TD]
[TD]n/a[/TD]
[TD]£4.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£4.00[/TD]
[TD="align: right"]£4.00[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]Amazon[/TD]
[TD]1000 Paper 50mm Diameter Round Mixed Colour Code Stickers[/TD]
[TD]LabelWonderland[/TD]
[TD]£5.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£5.00[/TD]
[TD="align: right"]£5.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£16.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Oscar Cronquist]

Formula in cell AA42:

=SUMPRODUCT(AA24:AA41/COUNTIF(U24:U41,U24:U41))

 

[MYUSERNAMEIS]

Formula in cell AA42:

=SUMPRODUCT(AA24:AA41/COUNTIF(U24:U41,U24:U41))

Thank you for such a quick reply!
I think this is getting close, however it seems to be including values that are hidden or filtered out - is there a way to only include visible cells?
Thank you for your help

 

[Oscar Cronquist]

You could use a helper column that tells the formula if the value is hidden.
Formula in cell AB24:
=SUBTOTAL(103,AA24)
Copy cell AB24 and paste to cells below.


Formula in cell AA42:
=SUMPRODUCT((AA24:AA41*AB24:AB41)/COUNTIFS(U24:U41,U24:U41,T24:T41,T24:T41)

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET($U$24,ROW($U$24:$U$41)-ROW($U$24),0,1)),IF(LEN($U$24:$U$41)>0,MATCH($U$24:$U$41,$U$24:$U$41,0))),ROW($U$24:$U$41)-ROW($U$24)+1),$AA$24:$AA$41))

 

[shaowu459]

Ctrl+shift+enter
=sum((match(u24:u41,u24:u41,)=row(u24:u41)-23)*(subtotal(3,offset(u23,row(u24:u41)-23,))=1)*aa24:aa41)