SUMIF until
 

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.

SUMIF until
 

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)),99 9,MATCH(A3,A4:A$65536,0)))
in G3 put
=IF(A3<2,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),99 9,MATCH(A3,A4:A$65536,0)))
in H3 put
=IF(A3<3,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),99 9,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...8&d=1144538649

--

Ed Wrote:
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 |
+-------------------------------------------------------------------+

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531188

SUMIF until
 

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

See attachment


--
Mandy11
------------------------------------------------------------------------
Mandy11's Profile: http://www.excelforum.com/member.php...o&userid=13000
View this thread: http://www.excelforum.com/showthread...hreadid=531188

SUMIF until
 

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...9&d=1144547812

--

Bryan Hessey Wrote:
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)),99 9,MATCH(A3,A4:A$65536,0)))
in G3 put
=IF(A3<2,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),99 9,MATCH(A3,A4:A$65536,0)))
in H3 put
=IF(A3<3,"",IF(ISERROR(MATCH(A3,A4:A$65536,0)),99 9,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...8&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 |
+-------------------------------------------------------------------+

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531188

SUMIF until
 

Thank you very much for this useful information!
,Ed

SUMIF until
 

Thanks for the response Ed

Ed Wrote:
Thank you very much for this useful information!
,Ed


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531188