• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match with two header criterias

Mange08

New Member
Hi,

I'm trying to sum a column based on two criterias.
It works well if I3=001 but not if I3=002

Uploading a mockup of the original file.
In the original report i have full year of months and weeks in row 1 and 2.
How should i write the formula?

month 001 001 001 002 002 002
week 01.2014 02.2014 Overall Result 03.2014 04.2014 Overall Result
100100 10 20 30 20 30 50 002 #REF!
100101 20 30 40 20 30 50
100102 30 40 50 20 30 50

=SUM(INDEX(B3:G5;;MATCH(I3;B1:G1;0)*MATCH("Overall Result";B2:G2;0)))
Skärmavbild 2014-10-22 kl. 20.33.20.png

Thanks in advance.
Magnus
 

Attachments

  • IndexMatch_2Headers.xlsx
    33.5 KB · Views: 7
Hi,

Try below formula:

=SUMPRODUCT(INDEX($B$3:$G$5,,LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1)))

Regards,

Hi, and many thanks!

Seems to work flawlessly =)
I'm gonna test it more tomorrow in the original workbook.

As i'm eager to learn more about this,
would you mind talk me through the formula, especially "LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1)))"
Lookup value, as long it's a positive number you can have any number or? why use 2?
COLUMN($B$1:$G$1)-COLUMN($B$1)+1)))"
Does this tell the formula to go down a row?

Thanks a lot for your solution.
Br
Magnus
 
Hi Magnus,

Glad you solved it. So here it goes. Basically I saw you said thar for 001 your formula is working, so I found you are summing the column of consolidated week.

So for week number we reuire the last column where there is a consolidation for various acct's nos.

=SUMPRODUCT(INDEX($B$3:$G$5,,LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1)))

So LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1) part in INDEX function returns column number with consolidated values.

Now LOOKUP function look's for a value in LOOKUP Vector and return the corresponding result from the result vector.

For e.g. you want to see the value in a vector {1;2;5;7} and return corresponding letter from result vector {"a";"b";"c";"d"}. So If I ask 2 so the result will be b and if I ask 7 the result will be d.

Note for getting a right result the lookup_vector must be sorted in ascending order.

Now comes a twist in LOOKUP function, if the the lookup value is not found in the lookup vector than the function return the value corresponding to the value which is less than the lookup value.

Say for above e.g. if you ask for lookup value 3, now 3 is not available in lookup vector, so the first value which is lesser than 3 i.e.2 the LOOKUP function will return the result corresponding to it i.e. b.

So basically COLUMN($B$1:$G$1)-COLUMN($B$1)+1 this part is the result vector which will generate an array of numbers like this.

COLUMN($B$1:$G$1) - this part gives array {2,3,4,5,6,7}

Now we subtract COLUMN($B$1) i.e. 2 from above vector so our array will become {0,1,2,3,4,5}. Now we will add 1 to this. So our final result vector will be {1,2,3,4,5,6}, which is equal to number of columns in the array B3:G3 of INDEX function.

Now Lookup Vector 1/(I3=$B$1:$G$1) will be 1 divided by an array of TRUE and FALSE so where ever there is TRUE we will get 1 and when it is FALSE we will get #Div/0 error.

So lets say I3 = 002, we will get a lookup vector like {#Div/0,#Div/0,#Div/0,1,1,1} in your file. Now the max number that the lookup vector can have is 1. So we are looking for a number greater than this i.e. 2 you can also use 3. So lookup Vector will return the last number which is less than this, so in all the cases we will get the column number corresponding to the consolidation of the week.

Hope this will solve your doubt, if not, than write back.

Regards,




 
Hi ,

If you are going to have the text "Overall Result" in row 2 , for every month , then you can simplify to :

=SUMPRODUCT(($B$1:$G$1=$I$3)*($B$2:$G$2="Overall Result")*($B$3:$G$5))

Narayan
 
Hi Magnus,

Glad you solved it. So here it goes. Basically I saw you said thar for 001 your formula is working, so I found you are summing the column of consolidated week.

So for week number we reuire the last column where there is a consolidation for various acct's nos.

=SUMPRODUCT(INDEX($B$3:$G$5,,LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1)))

So LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1) part in INDEX function returns column number with consolidated values.

/quote

Hi,

Thanks for your explanation, it looks so much easier for me now =)
And again, it does a good job in my workbook.

Br
Magnus
 
Hi ,

If you are going to have the text "Overall Result" in row 2 , for every month , then you can simplify to :

=SUMPRODUCT(($B$1:$G$1=$I$3)*($B$2:$G$2="Overall Result")*($B$3:$G$5))

Narayan

Thanks for your solution, it looks like something i tried but i didn't get it to work.
I will try this as well tomorrow.

Br
Magnus
 
Back
Top