Hello!
I am trying to find a formula that will allow me to perform a SUMIF function were the values to be summed are on another tab. That challenge is I have to use a MATCH function to locate the correct row without having to manual change the sum range each time I use the formula.
This is the formula I am using:
=SUMIF(A1:C1,G1,INDIRECT(ADDRESS(MATCH(F1,Sheet2!A:A,0),2,,,"Sheet2")&":"&ADDRESS(MATCH(F1,Sheet2!A: A,0),200,,,"Sheet2")))
Essentially that I am looking for is:
=SUMIF(A1:C1,G1,ADDRESS(MATCH(F1,Sheet2!A:A,0),2,,,"Sheet2":ADDRESS(MATCH(F1,Sheet2!A:A,0),200,,,"Sh eet2"))
The two address functions return the correct cell references, however the SUMIF function will not recognize the formula as valid.
I attached a spreadsheet with a model of what I am working on.
Your assistance is greatly appreciated. This has got me totally stumped!
Hi,
you should take sheet2 reference out of Address
Regards
Last edited by CANAPONE; 02-02-2012 at 02:08 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon below the post.
Please, mark your thread [SOLVED] if you received your answer.
Forum rules: http://www.excelforum.com/forum-rule...rum-rules.html
Hi,
a possible way
Hope it helps=SUMIF(A1:C1,G1,INDIRECT("Sheet2!"&ADDRESS(MATCH(F1,Sheet2!A:A,0),2)&":"&ADDRESS(MATCH(F1,Sheet2!A:A,0),200)))
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon below the post.
Please, mark your thread [SOLVED] if you received your answer.
Forum rules: http://www.excelforum.com/forum-rule...rum-rules.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks