Complicated SUMIF situation ):

Okay, so I have a sheet that contains a "tree" or hierarchy of a product and all the information about that product. In every row, there is a column called "Level" which indicates where in the tree the piece falls. For example:

Piece Price Level
Car / $1000 / 0
Door / $200 / 1
Window / $150 / 2
Lock / $50 / 2
Roof / $400 / 1
Antenna / $200 / 2
Bike Rack / $200 / 2

So what I want to do is make a macro that recognizes the change in level and SUMS the price of everything in that level. I tried simply using a SUMIF function, but I don't want to SUM every item that is a level 2, only the items that are level 2 UNTIL another level 1.

For example, if I SUMIF'ed the table above and used level 2 as criteria, it would give be $600 because it would do all the level 2's. I only want the level 2's bellow each level one. Because ideally, the price of a level 1 piece would be the SUM of all the pieces that make it up. How can I go about doing this? Is this even possible? Please help, thank you

Use Data>SubTotals, breaking on level.

Quote:

---

Originally Posted by xld

Use Data>SubTotals, breaking on level.

---

I tried that but it doesn't quite work right. It doesn't nest higher levels, and the way it displays the totals is a bit confusing for what I'm trying to go for. It sums up all the level 2's for example, but displays the total underneath them instead of in place of the level 1 item that is made up of the level 2's

I suggest you post a before and after example workbook of what you want.

Quote:

---

Originally Posted by xld

I suggest you post a before and after example workbook of what you want.

---

Here we go (: It seems simple enough, but I can't figure out how to do it /:

Here you are

[vba]

Public Function SetTotals()
Const FORMULA_TOTALS As String = "=SUMIF(C<first>:C<last>,<level>,B<first>:B<last>)"
Const FORMULA_LASTROW As String = "=MIN(IF(C<first>:C<last>=<level>,ROW(C<first>:C<last>)))"
Dim Lastrow As Long
Dim FinalRow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = 2 To Lastrow

If .Cells(i, "C").Value < .Cells(i + 1, "C").Value And _
i <> Lastrow Then

FinalRow = Application.Evaluate(Replace(Replace(Replace(FORMULA_LASTROW, _
"<first>", i + 1), _
"<last>", Lastrow), _
"<level>", .Cells(i, "C").Value))
If FinalRow = 0 Then FinalRow = Lastrow + 1
.Cells(i, "B").Formula = Replace(Replace(Replace(FORMULA_TOTALS, _
"<first>", i + 1), _
"<last>", FinalRow - 1), _
"<level>", .Cells(i, "C").Value + 1)
End If
Next i
End With
End Function[/vba]

Thank you very very much! I know this is going to sound incredibly newbie-ish, but how do i use this? If its a Public Function that means that I just put its name into the desired cell, right? Or is there someway to incorporate it into a macro?

Oh no, it is not a UDF. Either add a button to the worksheet and assign the function to that button, or run it directly from the VBIDE.

(I suppose a UDF might have worked, didn't think of that).

Oh dear. You cross-posted at Excel Forum http://www.excelforum.com/excel-gene...ml#post2583130

Worst thing is, you posted after I gave you a solution, so you clearly did not trust my help. You have posted two relatively difficult (certainly for you) questions here and we have answered both. So I guess I won't bother trying to help you again.

Wow this is perfect! I just got it to work properly. (Again, I apologize for my lack of knowledge) Is there anyway to use this function in a macro though? Because I need to do this for multiple sheets, and it would be easier if I could simply macro it instead of having to copy and paste into every sheet.
I have to also really thank you for this because this does exactly what I was looking for. I noticed that you saw my "cross-posting". I couldn't figure out how to use this function, and was too embarrassed to ask, so I thought I'd go somewhere else /: Either way, I figured it out and it works like a charm!