We help IT Professionals succeed at work.

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?
Comment
Watch Question

Top Expert 2016

Commented:
Hi,

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

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

Regards

Author

Commented:
This solution is throwing an error for me unfortunately.
Top Expert 2016

Commented:
And now

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

Author

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?
Top Expert 2016

Commented:
Here it is
EE20150311.xlsx

Author

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.
Top Expert 2016

Commented:
Could you send a dummy? because no file is available from your part

Author

Commented:
Must have not loaded initially.  Sorry.  Here it is.
Excel-issue-2.xlsx
Top Expert 2015

Commented:
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
Top Expert 2016

Commented:
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
Rob HensonFinance Analyst

Commented:
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.
Rob HensonFinance Analyst
Commented:
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

Author

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?
Top Expert 2015
Commented:
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

Top Expert 2016
Commented:
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

Author

Commented:
Great, thanks!