sumifs across multiple sheets

[gblum]

Hi I m trying to summarize data from several worksheets. I need to pull data on certain rows to match row titles and from columns that match column titles. On all the sheets the row titles are Grant, Gift, Institutional, Transfer. The column titles are month/yr covering different but overlapping periods on each sheet. From those different but overlapping months I only want to capture July 2013 through June 2014 on the summary sheet.

I've attached a sample xlsx. I manually calculated the expected totals in a separate column just for comparison on the summary worksheet.

Thanks,
Gary

Excel 2010
Intermediate Excel experience
no VBA

 

[SirJB7]

Hi, gblum!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, try this in summary worksheet:
E5: =SI.ERROR(INDICE(FUND1!$C$10:$N$13;COINCIDIR($C5;FUND1!$A$10:$A$13;0);COINCIDIR(E$3;FUND1!$C$8:$N$8;0));0)+SI.ERROR(INDICE(FUND2!$C$10:$N$13;COINCIDIR($C5;FUND2!$A$10:$A$13;0);COINCIDIR(E$3;FUND2!$C$8:$N$8;0));0)+SI.ERROR(INDICE(FUND3!$C$10:$W$13;COINCIDIR($C5;FUND3!$A$10:$A$13;0);COINCIDIR(E$3;FUND3!$C$8:$W$8;0));0) -----> in english: =IFERROR(INDEX(FUND1!$C$10:$N$13,MATCH($C5,FUND1!$A$10:$A$13,0),MATCH(E$3,FUND1!$C$8:$N$8,0)),0)+IFERROR(INDEX(FUND2!$C$10:$N$13,MATCH($C5,FUND2!$A$10:$A$13,0),MATCH(E$3,FUND2!$C$8:$N$8,0)),0)+IFERROR(INDEX(FUND3!$C$10:$W$13,MATCH($C5,FUND3!$A$10:$A$13,0),MATCH(E$3,FUND3!$C$8:$W$8,0)),0)
and copy across and down as required.

Just advise if any issue.

Regards!

 

[Debraj]

Hi gblum!

Welcome to the forum!..

I have lil bit doubt on your manual calculation, and I am not even found any data in all three sheets which are from Jul 14 to Jun 15 ( which you have mentione in your upload)..

However if you change them to Year 13 to 14 (as per your post), its working
=SUM(SUMIF(INDIRECT("'"&SheetList&"'!6:6"),E$1,INDIRECT("'"&SheetList&"'!"&ROW()+5&":"&ROW()+5)))

BTW, i have used few Helper Column.. Lemme try, If i can avoid it..

OOPS @SirJB7 already did the job..

 

[SirJB7]

@Debraj(ex-Roy)
Hi, buddy!

Take care of 4 things:
- FUND2 worksheet data range goes up to column W instead of N
- Summary worksheet dates should be subtracted 1 year, otherwise no data will match
- FUNDn worksheets values should be easier-identifiable, otherwise you'll sum them, not me
- I thought about INDIRECT approach but have problems with Total column, so I worked hard, so hard that I'm tired and must go for a Carlsberg

For this last I used this formula to replace all the posted values:
=AÑO(C$8)*10000+MES(C$8)*100+FILA() -----> in english: =YEAR(C$8)*10000+MONTH(C$8)*100+ROW()

Give a look at the uploaded file.

Regards!

 

[gblum]

Hi all,

Thanks for your help so far.

On my sample workbook, the manual calculation is correct, but Debraj you are correct about the month/year on my summary page. It should have started with 7/31/2013, and not 7/31/2014.

SirJB7 is very close to what I need. I changed the MATCH part of his formula so the end range of the match is $AZ. This change worked fine.

=IFERROR(INDEX(FUND1!$C$10:$AZ$13,MATCH($C6,FUND1!$A$10:$A$13,0),MATCH(E$3,FUND1!$C$8:$AZ$8,0)),0)+IFERROR(INDEX(FUND2!$C$10:$AZ$13,MATCH($C6,FUND2!$A$10:$A$13,0),MATCH(E$3,FUND2!$C$8:$AZ$8,0)),0)+IFERROR(INDEX(FUND3!$C$10:$AZ$13,MATCH($C6,FUND3!$A$10:$A$13,0),MATCH(E$3,FUND3!$C$8:$AZ$8,0)),0)

Now that the match range is standardized for all sheets, can the FUND# be written as a range, something like FUND1:FUND3? The reason for this is my actual workbook has 40-50 sheets, with each sheet potentially covering different monthly date ranges. Sheets are added and deleted regularly and new grants are funded or spent out.

Thanks again for your effort so far.

Gary

 

[SirJB7]

Hi, gblum!

3D references (multiple worksheet references) are not widely supported and are only available for a few functions, being actually are a bit tricky to setup workarounds. For additional information check these links:
https://office.microsoft.com/en-us/...346.aspx#BMlearn_more_about_a_3-d_refererence
http://forum.chandoo.org/threads/sum-if-with-index-match.2470/

Now in your case you can try with this adaptation of @Debraj(ex-Roy)'s prior formula accordingly with that of @Sajan:
E5: =SUMA(SUMAR.SI(INDIRECTO("'"&WSList&"'!3:3");E$3;INDIRECTO("'"&WSList&"'!"&FILA()+0&":"&FILA()+0))) -----> in english: =SUM(SUMIF(INDIRECT("'"&WSList&"'!3:3"),E$3,INDIRECT("'"&WSList&"'!"&ROW()+0&":"&ROW()+0)))

where the "+0" for both ROW() functions are to properly offset the data between summary and child worksheets. I set up them to zero as I standardized all the worksheets. There's also a new worksheet WS to hold the worksheet names to be considered (dynamic named range WSList).

Sample file fixed and uploaded.

Regards!

 

[gblum]

Hi SirJB7,

That seems to work fine and adding deleting new sheet names to the WS tab is no big deal. I find I learn by studying excel formulas. There's a lot to learn here.

Thank you all again.

Gary

 

[SirJB7]

Hi, gblum!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!

 

[gblum]

Can you tell me how you named the cells "FUND1, FUND2, FUND3" in the WS sheet? I thought that was by defining the name, but that doesn't seem to work when I try to rename the cells.
Thanks,
Gary

 

[SirJB7]

Hi, gblum!

I'm installing a new computer and for the time being I don't have Office suite installed (due to a personal debate on 2013 version installation or not) so I can't check Excel files.

But if I don't remember wrong, weren't FUND1/2/3 the data (non summary) worksheet names? If so aren't their names entered in the named range WSList? In that case there's no need to further definition or naming, INDIRECT function does the job.

Does this help? Sorry for not being able to answer more accurately at this moment. If required, tomorrow I'd do it.

Regards!