# Formula Challenge 024 - Sum Of Items Based On Hierarchy Level

#### Sam Mathai Chacko

##### Active Member
So we haven't had a new challenge in a while, so I thought I'll remind people that we have a section of the forum to pick your brains, by posting the following formula based challenge. I came across this in VBAExpress; so the credit for the question goes to the OP. So anyway, the challenge is non-VBA, and it goes like this

Illustration: You have a list of items and their prices in columns B and C. In column A, the levels are indicated as .[x]#, where .[x] represents the level of the hierarchy, and # is the number which represents that hierarchy. So if one were to identify the hierarchy, you could either count the number of dots/periods/decimals (whatever you want to call it), or extract the numeric value on the right hand side of the value in Col A.

Note that there is no price value for the top-most hierarchy, simply because that's the highest level. By the way, column B is irrelevant in our question.

The sample output is shown in column D, and this you can use as reference. What we are looking for is a formula which does not require a helper column.

You can assume that there is no data below this table.

And if it's any motivation, I cooked up a simple array formula that is 98 characters long.

#### Attachments

• 13.2 KB Views: 139
Last edited:
• achu and jeffreyweir

#### NARAYANK991

##### Excel Ninja
Hi Sam ,

Can you please post a workbook with the data , so that everyone does not have to go through the same effort in setting it up ?

Not that I am going to attempt this challenge , but it may help others.

Narayan

#### Sam Mathai Chacko

##### Active Member
Sure Narayan. File attached in OP.

• NARAYANK991

#### NARAYANK991

##### Excel Ninja
Hi Sam ,

This seems to give the same results for the test data ; is it correct ?

=IFERROR(SUM(OFFSET(C2,,,MATCH(TRUE,RIGHT(A3:\$A\$20)<=RIGHT(A2),0))),SUM(C2:\$C\$20))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan

• r1c1 and Sam Mathai Chacko

#### Sam Mathai Chacko

##### Active Member
Narayan, that looks very much right. And shorter too. Great ... any other attempts anyone?

#### Sam Mathai Chacko

##### Active Member
I might add that the only problem being, if the level exceeds to two digits, like 10, 11 or above, then the solution might not work exactly as intended

#### NARAYANK991

##### Excel Ninja
Hi Sam ,

Will this work ?

=IFERROR(SUM(OFFSET(C2,,,MATCH(TRUE,RIGHT(A3:\$A\$20,2)*10<=RIGHT(A2,2)*10,0))),SUM(C2:\$C\$20))

Narayan

• Faseeh

#### Sam Mathai Chacko

##### Active Member
Narayan

Of course that will work, but again, the idea is that the levels could be as long is it can... maybe 3 digits or 4 digits. Your original formula is fundamentally correct, but maybe could be a little more generic, to the extend that it considers any number of levels, and not just up to 99. Hope that makes sense. #### Sam Mathai Chacko

##### Active Member
No takers? I understand it's relatively simple compared to a lot of the other formula challenges, but that shouldn't make you abstain!

Well, I'll go ahead and post my solution

=SUM(OFFSET(C2,,,MATCH(0,--(--SUBSTITUTE(0&A3:A\$22,".","0")>--SUBSTITUTE(A2,".","0")),)))

• Faseeh and NARAYANK991

#### Faseeh

##### Excel Ninja
Hi Sam,

I have been trying using SUBSTITUTE() but was unable to focus. Good Solution.

#### Sam Mathai Chacko

##### Active Member
Thanks Faseeh for the feedback.

Just realized that I could take out 5 more characters =SUM(OFFSET(C2,,,MATCH(0,N(--SUBSTITUTE(0&A3:A\$22,".",0)>--SUBSTITUTE(A2,".",0)),)))

• Faseeh

#### Sam Mathai Chacko

##### Active Member
This keeps getting better. 64 Characters

=SUM(OFFSET(C2,,,IFERROR(MATCH(0,N(LEN(A2)<LEN(A3:A\$20)),),19)))

• Ananda Kumar C

#### r1c1

Staff member
@Emmanuel Guido Interesting variation of original question. Here is one formula that seems to work ok.

Assuming your data is in range A2:B11, you can use below formula. =IF(B2<>"",B2,SUM(B2:INDEX(B2:\$B\$11, MATCH(A2,A3:\$A\$11,0))))

We essentially get the value in column B if one is present.
Else, we look for next cell with same level and add up values up to that cell from current cell (using a combination of relative and absolute references)

For more on the usage of INDEX in a reference, see this page:

http://chandoo.org/wp/2013/09/18/index-formula-usage-and-tips/

• Emmanuel Guido

#### Emmanuel Guido

##### New Member
Hi.

Thanks for the feedback!

Yes, the formula is great. Just need to add error handling if no Match found.

Anyway, the formula I needed was a bit more complicated. The source data I have sometimes has amount at a level with lower levels, and it may/may not be correct (e.g. level 2 has 999 but sum of level 3 is 1000). See updated sample below and the formula I used. (it is quite crude but works anyway, and I would not have solved it without your feedback, thanks again.) =IF(AND(B2<>"", B2>SUMIFS(C2:INDEX(C3:\$C\$11,IFERROR(MATCH(A2,A3:\$A\$11,0),0)),A2:INDEX(A3:\$A\$11,IFERROR(MATCH(A2,A3:\$A\$11,0),0)),A2+1)),B2,SUMIFS(C2:INDEX(C3:\$C\$11,IFERROR(MATCH(A2,A3:\$A\$11,0),0)),A2:INDEX(A3:\$A\$11,IFERROR(MATCH(A2,A3:\$A\$11,0),0)),A2+1))

Regards,

#### vivekgoyal

##### New Member
Hi @Emmanuel Guido,
A small attempt from my side for this problem,

=IF(A2<A3,SUMIF(OFFSET(A3,,,IFERROR(MATCH(A2,A3:A\$11,0),COUNT(A3:A\$11))),A2+1,C3:C\$11),B2)

Assuming your data is in range A2:B11, you can use below formula.
We take the value in column B if row below has same or higher level.
Else, we look for next cell with same level and add up values up to that cell from cell below.

Cheers!
Vivek

• Emmanuel Guido

#### Subrat Mandal

##### New Member
I know there are few shorter approach already posted above, I just wanted to give it a try:
{=IFERROR(SUM(OFFSET(C2,,,MATCH(1,IF(VALUE(SUBSTITUTE(A2,".",""))<VALUE(SUBSTITUTE(A3:\$A\$20,".","")),0,1),0))),SUM(C2:\$C\$20))}