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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rgonzo1971Commented:
And now

=SUMIF(INDIRECT("'Jan-"&A1&"'!A1",TRUE),">0")
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BBRRGGAuthor Commented:
Great, thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.