sum until criteria not met

[JB2010]

hi there


i am looking for some amendment to a SUMIF or SUMPRODUCT.

i have a table of that shows data in columns going down in rows in the order
of a product hierarchy, like this;

A B
1 Product #
-----------------------------
2 All Products 250
3 Cars 100
4 Car A 50
5 Car B 30
6 Car C 20
7 Trucks 150
8 Truck A 90
9 Truck B 35
10 Truck C 25

there are three levels to the hierarchy; top (All Products), middle (Cars /
Trucks) & bottom (A/B/C). the bottom level is inputted data, the middle level
should be a subtotal of the bottom level & the top level should be a total of
the middle level.

i am looking for a formula i can put in a third column that will check the
totals & subtotals at top & middle levels of the hierarchy.

I order to do this, i presume i will need to tell the function to look down
the rows for sequential items of a similar level & sum the range up to the
point where the next row is of a different level. the problem is, i dont know
how to do this!?!?

Any one got any ideas?

I have tried to simplify this from the actual problem, but if i have been
unclear or you need any more info, please do not hesitate to let me know

many thanks

jb

 

[Toppers]

A possible solution but maybe it's too "simple" for your real data:

All Products:

=SUMPRODUCT(--($A$3:$A$10={"Cars","Trucks"})*($B$3:$B$10))

Cars:

=SUMPRODUCT(--(LEFT($A$3:$A$10,4)="Car "),$B$3:$B$10)

Trucks:

=SUMPRODUCT(--(LEFT($A$3:$A$10,6)="Truck "),$B$3:$B$10)

You may be able to adapt to match the real data.

 

[Bob Phillips]

The All Products is easy.

For the middle tire, try this in row 2 and copy down

=IF(ISNUMBER(FIND(" ",A2)),"",SUMIF(A:A,LEFT(A3,FIND(" ",A3))&"*",B:B))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JB2010]

Hi there


that works great, but is there anything I can do when the bottom level names
bear no resemblence to the next level up in the hierarchy, like this:


A B
1 Product #
-----------------------------
2 All Products 250
3 Cars 100
4 Porsche 50
5 Datsun 30
6 Yugo 20
7 Trucks 150
8 Daf 90
9 Scania 35
10 Luton 25

 

[Toppers]

I have a not very elegant solution using helper columns:

H1:H3 contained the names of the intermediate levels :"Card","Trucks" and
"END"

The last row in column A should contain "END"

in C1: =IF(ISNA(MATCH(A1,$H$1:$H$3,0)),"",MATCH(A1,$A$1:$A$13,0)) and copy
down until "END" row

in D1: =IF(ROW()<=COUNT($C$1:$C$13),N(SMALL($C$1:$C$13,ROW())),"")

Copy down until you get a blank cell

in E1:
=IF(ISNUMBER($C1),SUM(OFFSET($B$1,$C1,,INDIRECT("D"&(COUNT($C$1:$C1)+1))-C1-1)),"")

Copy down until penultimate row in column A

You can change the helper columns to other than C& D or hide them.

HTH

 

[Bob Phillips]

It would be very difficult to know where to stop counting in this scenario.
How do you know that Trucks is not a car type?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)