Excel- reference a tab name in a SUMIF formula

Usually when referencing a tab name in a formula, we'd use:
='tab1'!A1.

Or, in my sheet (attached), since my tabs are named for dates:
='Mar-15'!A1.

On the main tab are SUMIF calculations, a new date for each column.  Since each date will also get a new tab of data (to be summed), I'd like the formulas to account for the dated tabs.  Rather than hard coding 'Mar-15' or 'Mar-16' or 'Mar-...X' into the SUMIF formulas, looking to reference the dates in the column headings in 'Main tab'.  

Can this be done?
BBRRGGAsked:
Who is Participating?
 
Rgonzo1971Commented:
with my version

it would be

=SUMIF(INDIRECT(TEXT(B$1,"'d-MMM'")&"!$A:$A"),'Main tab'!$A2,INDIRECT(TEXT(B$1,"'d-MMM'")&"!$B:$B"))

Regards
0
 
Rgonzo1971Commented:
Hi,

pls try ( replacing the first A1 with reference to year)

=+SUMIF(INDIRECT("'Jan-"&A1&"'!A1";TRUE);">0")

Regards
0
 
BBRRGGAuthor Commented:
This solution is throwing an error for me unfortunately.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Rgonzo1971Commented:
And now

=SUMIF(INDIRECT("'Jan-"&A1&"'!A1",TRUE),">0")
0
 
BBRRGGAuthor Commented:
Sorry this is still not working for me.  Can you please add it to the Excel file as you describe?  And then upload the file?
0
 
Rgonzo1971Commented:
Here it is
EE20150311.xlsx
0
 
BBRRGGAuthor Commented:
Thanks.  I see in this file what you've done.  It's quite nice, but it does not solve my question.  If you'd like to edit the Excel file I uploaded to show your method & solve the initial question, please do so.  Otherwise, I do appreciate your taking the time to offer this method.
0
 
Rgonzo1971Commented:
Could you send a dummy? because no file is available from your part
0
 
BBRRGGAuthor Commented:
Must have not loaded initially.  Sorry.  Here it is.
Excel-issue-2.xlsx
0
 
Saurabh Singh TeotiaCommented:
I will use the following formula to do what you are looking for...

=SUMIF(INDIRECT("'"&TEXT(B$1,"dd-mmm")&"'!$A:$A"),'Main tab'!$A2,INDIRECT("'"&TEXT(B$1,"dd-mmm")&"'!$b:$b"))

Open in new window


Enclosed is your workbook for your reference...

Saurabh...
Excel-issue-2.xlsx
0
 
Rgonzo1971Commented:
pls try

=SUMIF(INDIRECT(TEXT(B$1,"'dd-MMM'")&"!$A:$A"),'Main tab'!$A2,INDIRECT(TEXT(B$1,"'dd-MMM'")&"!$B:$B"))
Excel-issue-2V1.xlsx
0
 
Rob HensonFinance AnalystCommented:
Once again, another alternative; I think you can use the OFFSET function to create a range to SUM.

I will take a look at the file and comment again if possible.
0
 
Rob HensonFinance AnalystCommented:
As I thought, using OFFSET is possible:

=SUMIF(OFFSET(INDIRECT(ADDRESS(1,1,1,1,TEXT(B$1,"dd-mmm"))),0,0,100,1),$A2,OFFSET(INDIRECT(ADDRESS(1,2,1,1,TEXT(B$1,"dd-mmm"))),0,0,100,1))

Thanks
Rob
0
 
BBRRGGAuthor Commented:
These are all great, thank you!
One thing is that some days in the month are only d-mmm.  I have to go in manually and change the formula, d-mmm for the first 9 days of each month, and then dd-mmm for the remainder of the month.  Is there a way to account for this in the formulas?
0
 
Saurabh Singh TeotiaCommented:
You can simply use..

=SUMIF(INDIRECT("'"&TEXT(B$1,"d-mmm")&"'!$A:$A"),'Main tab'!$A2,INDIRECT("'"&TEXT(B$1,"d-mmm")&"'!$b:$b"))

Open in new window

0
 
BBRRGGAuthor Commented:
Great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.