• 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 Sum

N

nadjalil

Member
Hello everyone

I am back with another challenge, In attached file I have index and match function which is working fine till the time I need a specific figures to bring in sheet IS-F but now I need to sum all those numbers which are lying under 2010 and 2009 which belongs to criteria F in IS-F, can somebody help to find how to put sum with index and match…


Thank you all the gurus out there who provide the great help, thank you so much

Kind Regards
 

Attachments

  • Sum with index match.xlsx
    74.5 KB · Views: 11
Hi ,

Can you explain , with reference to the file you have uploaded , what figure should appear in cells C6 , C9 , C10 in the tab IS-F ?

Narayan
 
Hello
hope you are doing fine, thanks for the reply, these cells should have the sum of criteria 'F' on all the sheets X,Y,Z but under column head Actual 2010, but sir if you give a formula for 2009 column i can manage to do for 2010 column myself.
thanks for your help.
 
Hi nadjalil,

For 2009 column use below array formula in D6 and copy down.

=SUM(IF(INDIRECT(""&$A6&"!A1:A1000")=$A$1,INDIRECT(""&$A6&"!C1:C1000")))

For 2010 change the bold part to B1:B1000.

Regards,
 
Hi Somendra
thanks for your solution but i need to sum it based on two condition and one is fixed which is in Cell A1 and second one varying in columns e.g. C3 D3 and so on as the header are the same in all the files but we move them time to time on working sheet which are in purple.
i hope i explain properly and get an help to resolve this problem.

Kind Regards
 
Hi ,

See your file now. I have put the formulae in columns F and G. The formula assumes that your headers will always be in row 3 on all the tabs.

Narayan
 

Attachments

  • Sum with index match.xlsx
    74.6 KB · Views: 14
Dear Sir,
thanks for the solution, i'll check it on my actual file and let you know...
thanks for your kind help.
-Nadeem
 
Dear Sir
thanks for your help, i tried on my actual file but it didnt work, it'll be great if you could please explain this formula step by step and that may help me to resolve this on my actual file.
thanks once again for your precious help.

Kind Regards
 
Hello Nadeem,

You can use INDEX/MATCH to check where header is located in sheet entered in A6 to down. So try this in C6, then copy down & across

=SUMIF(INDIRECT("'"&$A6&"'!A:A"),$A$1,INDEX(INDIRECT("'"&$A6&"'!A:IV"),0,MATCH(C$3,INDIRECT("'"&$A6&"'!A3:IV3"),0)))

Assuming header are always will be in row 3 in all sheets, if not change row number as in red highted
 
You must log in or register to reply here.
Back
Top