SUMIF until

[Ed]

Hello, I have a budget and i have set a column on the side to assign a
hierarchy (here shown in [] ) , which also gives a conditional formatting
format, for example:

[H] / WBS / Concept / (Format)

[1] / 1 / CONSTRUCTION / (Fill: Black, Font: White, Bold)
[2] / 1.1 / Floor slab / (Fill: Grey, Font: Black, Bold)
[3] / 1.1.1 / 10 cms. slab / (Fill: none, Font: Black)
[3] / 1.1.2 / 15 cms. slab / (Fill: none, Font: Black)
[2] / 1.2 / Walls / (Fill: Grey, Font: Black, Bold)

How can I tell to Excel that the unit price of [1] is the sum of all [2]'s
until i get to the next [1]. The unit price of [2]'s is equal to the sum of
all [3]'s until I get to a [2] or a [1]? So in that way I just assign the
numbers and I am automatically generating subtotals and so on?

,thanks.

 

[Bryan Hessey]

Assuming your data starts at row 3, enter unit prices for the base item
in column E
in F3 put

=IF(A3<>1,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),999,MATCH(A3,A4:A$65536,0)))
in G3 put
=IF(A3<>2,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),999,MATCH(A3,A4:A$65536,0)))
in H3 put
=IF(A3<>3,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),999,MATCH(A3,A4:A$65536,0)))
in I3 put
=IF(A3<>1,"",E3+SUM(OFFSET(J3,0,0):OFFSET(J3,F3,0)))
in J3 put
=IF(A3<>2,"",E3+SUM(OFFSET(K3,0,0):OFFSET(K3,G3,0)))
in K3 put
=IF(A3<>3,"",E3)

Select (highlight) F3 to K3 and formula-drag the 6 columns to the end
of your data.
You can then Hide columns F,G & H, also perhaps J & K

A sample file is attached at
http://www.excelforum.com/attachment.php?attachmentid=4608&d=1144538649

--

Hello, I have a budget and i have set a column on the side to assign a
hierarchy (here shown in [] ) , which also gives a conditional
formatting
format, for example:

[H] / WBS / Concept / (Format)

[1] / 1 / CONSTRUCTION / (Fill: Black, Font: White, Bold)
[2] / 1.1 / Floor slab / (Fill: Grey, Font: Black, Bold)
[3] / 1.1.1 / 10 cms. slab / (Fill: none, Font: Black)
[3] / 1.1.2 / 15 cms. slab / (Fill: none, Font: Black)
[2] / 1.2 / Walls / (Fill: Grey, Font: Black, Bold)

How can I tell to Excel that the unit price of [1] is the sum of all
[2]'s
until i get to the next [1]. The unit price of [2]'s is equal to the
sum of
all [3]'s until I get to a [2] or a [1]? So in that way I just assign
the
numbers and I am automatically generating subtotals and so on?

,thanks.

+-------------------------------------------------------------------+
|Filename: Costs.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4608 |
+-------------------------------------------------------------------+

 

[Mandy11]

You have to write the sum formula manually for each category. You could
write it with an "if" condition if you like.

See attachment

 

[Bryan Hessey]

For amusemennt I have also generated the paragraph number you have in
the second column, to save you re-typing if you insert a new item
portion.

Attachment as
http://www.excelforum.com/attachment.php?attachmentid=4609&d=1144547812

--

Assuming your data starts at row 3, enter unit prices for the base item
in column E
in F3 put

=IF(A3<>1,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),999,MATCH(A3,A4:A$65536,0)))
in G3 put
=IF(A3<>2,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),999,MATCH(A3,A4:A$65536,0)))
in H3 put
=IF(A3<>3,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),999,MATCH(A3,A4:A$65536,0)))
in I3 put
=IF(A3<>1,"",E3+SUM(OFFSET(J3,0,0):OFFSET(J3,F3,0)))
in J3 put
=IF(A3<>2,"",E3+SUM(OFFSET(K3,0,0):OFFSET(K3,G3,0)))
in K3 put
=IF(A3<>3,"",E3)

Select (highlight) F3 to K3 and formula-drag the 6 columns to the end
of your data.
You can then Hide columns F,G & H, also perhaps J & K

A sample file is attached at
http://www.excelforum.com/attachment.php?attachmentid=4608&d=1144538649

I would note that your data did not fit your question, in that the cost
of Floor Slab would not really seem to be the cost of 10 cm + the cost
of 12 cm slab, perhaps you need to re-arrange that portion.

--

+-------------------------------------------------------------------+
|Filename: Costs.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4609 |
+-------------------------------------------------------------------+

 

[Ed]

Thank you very much for this useful information!
,Ed

 

[Bryan Hessey]

Thanks for the response Ed

Thank you very much for this useful information!
,Ed