Using criteria with dynamic range

[zilch42]

Hi there, I hope the following is not too confusing

I have some data organised in a hierarchy format for a life cycle inventory which I am having trouble sub totalling on different hierarchy levels

It is basically organised as such:

			Value	Level	Subtotal
Level 1				1
	Level 2		300	2
	Level 2			2
		Level 3	25	3
		Level 3	140	3
	Level 2		80	2
Level 1				1
	Level 2			2
		Level 3	40	3

I am using the level column to lookup the hierarchy level and use it in my calculations.

Where a level has no subcategories, it has it's own value. Where a level does have subcategories (as with the second level 2 entry, it has 2 level 3 entries below it) I want the subtotal column to sum those levels.

So far I have got some of this to work. I can get the level 2 categories which are followed by a level 3 category to sum all of the level 3 categories using sumproduct and generating true/false arrays to multiply by the values, but I am having trouble getting the formula to stop. Ie, I want the level 2 subtotal to include the level 3 lines below it, but I don't want to include the level 3 line on the last row because that belongs to a different level 2 category.

My thought on doing this was to try and create a criteria that tested whether the min of all index levels between the row containing the formula and the row considered for summation were greater than the index level containing the formula. Ie, if we're summing level 3's, is there a 2 above this cell? in which case, stop summing 3's. That however requires the range to be tested to grow as we go further down the sheet.

Does that make sense?
Any help would be greatly appreciated

Tad

[Sindhus]

Could you upload a excel file with your expected results?

[FDibbins]

as siggested, upload a sample please. but you may also want to look into trying the sumifS() function?

[zilch42]

Example.xlsx
An example is attached, with the results I want.
I know I could probably use a pivot table to achieve what I have asked, but there is some other other intermediate calculation that I haven't shown so as not to complicate things, which I think excludes the use of a pivot table

[zilch42]

Thanks for the suggestion, I hadn't used SUMIFS before.

The problem I ran into with the regular SUMIF was that it didn't like cell references in the criteria. It's happy with ">1" but doesnt like ">H3+1", which is what I really want as the criteria changes depending on the level we are on.

Is there a way to have the criteria change like that?

[Sindhus]

Try this array formula.. it gives output as per your attached sheet. Enter this in I3 and drag it to other cells.

Please Login or Register  to view this content.

Entered with ctrl+Shift+Enter

[zilch42]

Thanks Sindhus, that formula is really cool. Is there a function I could use to return the range returned by the INDIRECT function as a vector of the values in the range? I would like to use it inside a sumproduct function

[Sindhus]

I dont think it will work. Tell your requirement. There may be other solutions.

[zilch42]

I'll do up another example with the rest of the calculations I want to do.
Can you explain to me now though, why in some cases the INDIRECT function puts {} around the input arguments (I can see it when I step through the formula), resulting in a #VALUE! error, and sometimes it doesn'? It seems to depend on what function the INDRIECT function is part of

for example,
=SUM(INDIRECT("G3:G11")) evaluates fine
=IF(1=1,SUM(INDIRECT({"G3:G11"})),0) goes to =IF(1=1,SUM(INDIRECT(#VALUE!)),0)
=SUMPRODUCT(INDIRECT({"G3:G11"})) goes to =SUMPRODUCT(INDIRECT(#VALUE!))

[zilch42]

I have attached a new example with the extra steps. Basically the value of each sub-category is presented on some basis (eg. the sub categories on level 3 might be in terms of a value per kg of sub-category 1, but we want 2.6kg of subcategory 1).

So at each subtotal there is an adjustment of the value depending on the basis and the amount required.

I have also included the formula that I have been trying to use, which just needs an extra set of criteria to tell it to stop summing when we start a new category.

But if there is a better way entirely, then I'm open to it

Thanks Example2.xlsx

[Sindhus]

Try this formula in K3 and copy it to other cells.

Please Login or Register  to view this content.

Confirmed with Ctrl+Shift+Enter

The value will show the correct value, after copying it to other cells only.

[zilch42]

It works!! I don't understand it but it works Thank you

For my own learning, can you explain to me what's going on with the INDIRECT function? I step through it and both of them are returning INDIRECT({"#VALUE!"}), but then they both return a number and the function still works.

Then I'll mark it as solved.
Thanks so much

[Sindhus]

You cannot learn array formula using evaulate formula option.
1. IF(J4:$J$28<=J3,ROW(J4:$J$28)) - for each cell in J4:J28, if it is less than J3, it will return the row number of the cell, else false. To see this, take a empty column say col M. Select M4:M28, type this formula and press ctrl+Shift+Enter.
2. next im taking smallest of this array and doing the other calculation.
Only point no. 1 is array, all others are ordinary formula. Hope you can understand the rest of the formula.

[zilch42]

Ok that makes sense. Thanks again Great work