SUM until condition met

[chulho]

hey guys,

i can't seem to solve this seemingly simple problem.

i want SUBTOTAL column to display what's below, by adding up all the cost of the main product.

i tried messing around with index and match with if, but can't get it all to work.

anyone with a simple solution?

file is attachedSUM until.xlsx

MAIN PRODUCT SUB PRODUCT COST SUBTOTAL
BREAKFAST 10 10
SERVICE 75
BAR 30
RECEPTION 45
DINNER 50 50


*SUBTOTAL IS DISPLAYED ONLY ON THE THE MAIN PRODUCT ROW
*SUBTOTAL IS CALCULATED BY SUM OF ALL COST UNTIL NEW PRODUCT IS ENCOUNTERED ON COLUMN A

[simarui]

I got a strange error while trying to save your file... but i believe I accomplished your goal with a 'helper formula' in cell F2 copied down:

Please Login or Register  to view this content.

and the following in your subtotal column, (cell D2 copied down):

Please Login or Register  to view this content.

[simarui]

Also... you need to add another word below Dinner...

[chulho]

nice! thanks for that. will have to study up on match and offset as i'm not too familiar with them.

but is there an easy fix for NOT adding a text below the last entry (dinner) to make this work?

thanks for the help.

[XOR LX]

Hi,

In D2 and copy down:

=IF(A2="","",SUMPRODUCT((LOOKUP(ROW($A$2:$A$6),(1/($A$2:$A$6<>"")*ROW($A$2:$A$6)),$A$2:$A$6)=A2)*$C$2:$C$6))

Regards

[chulho]

wow.. it works!
thanks!!

time to study those formulas...

[XOR LX]

You're welcome.

[chulho]

hey guys,

i revived the thread because need some more help~

the solution provided here works great if i create simple lists. but for some reason trying to insert this formula in the form i'm working on (so_test.xlsx), returns (#value!).

i tried working backwards, simplifying the form by deleting all unnecessary fields, but for some reason it keeps returning #value!.

i'm attaching two files: data_test.xlsx needs to be opened first because so_test.xlsx refers to it.

i highlighted 3 columns in yellow:

column c: products
column o: cost
column aa: where i want the grouped totals to display

i've already put in the formula by XOR LX in column aa, which is displaying "value#!".

i've been working on this for many hours now and just can't make any progress. help is very much appreciated!!

[XOR LX]

Hi,

SUMPRODUCT will not sum arrays containing text entries (the "" as a result of your formulas in LIST2).

Perhaps change your formula in O18 to:

=IF(F18="",0,F18*M18)

and, if you wish, use a custom number format or conditional formatting to mask zero values.

Regards

[chulho]

thanks again!

worked perfectly!