We help IT Professionals succeed at work.

# Excel- reference a tab name in a SUMIF formula

on
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

## View Solutions Only

Top Expert 2016

Commented:
Hi,

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

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

Regards

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

Commented:
And now

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

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

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

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"))
``````

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
Finance 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.
Finance Analyst
Commented:
As I thought, using OFFSET is possible:

Thanks
Rob

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"))
``````
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

Commented:
Great, thanks!