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

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.

"JB2010" wrote:

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

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" wrote in message
...
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

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

--------------------------------


"Bob Phillips" wrote:

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" wrote in message
...
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

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,RO W())),"")

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

"JB2010" wrote:

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

--------------------------------


"Bob Phillips" wrote:

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" wrote in message
...
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

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)

"JB2010" wrote in message
...
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

--------------------------------


"Bob Phillips" wrote:

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" wrote in message
...
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