Calculating SUMIF across different worksheets

[RoversFan]

Hello!

I wonder if someone can help me?

I have a work book with a summary sheet and also several data sheets.
In each data sheet I have a cell (A1) with a value in it. In B1 there is a date.

I need to find a way to get a total in the summary sheet of the total of A1's in all sheets if the corrisponding date in B1 is within a year of todays date.

E.G. If I have 10 sheets and 6 of them have dates that fall within a year, I need the total of the value for those 6 sheets.

Is there a way of doing this?

 

[Dave Patton]

try
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B1"),">="& C12, INDIRECT("'"&SheetList&"'!A1")))

SheetList is a named range that lists your Sheet Names.
Cell C12 contains a threshold date ( 1 year ago)

 

[texasalynn]

welcome to MrExcel board . . .

take a look at these:
http://www.mrexcel.com/forum/showthread.php?t=379597
http://www.mrexcel.com/forum/showthread.php?t=119020
http://www.mrexcel.com/forum/showthread.php?t=378414

HTH

 

[RoversFan]

Thanks for the replies. I tried that formula, however it didn't seem to work.

Where you've put sheet list, what do I replace that with?

My sheets look like this:

First (hidden), Summary, Data a, Data b, Data c... etc..., Last (hidden)

Do I write first:last! where you've writen ("'"&SheetList&"'!B1") e.g. ("first:last!B1")

 

[Dave Patton]

"SheetList is a named range that lists your Sheet Names"

Somewhere on your sheet say E1:E10 list your sheet names i.e. Data a in E1 etc.
Name this range SheetList.

If you use the add-in Morefunc, we can use the First:Last concept; please advise.

Dave

 

[RoversFan]

Ahh ok, I understand.

The problem is I'll be adding new sheets fairly often, would I have to update my range each time?

 

[Dave Patton]

Ahh ok, I understand.

The problem is I'll be adding new sheets fairly often, would I have to update my range each time?

Yes, unless you utilize the Morefunc Add-in.

 

[RoversFan]

Work restrict what addins I can use.

Hmmm... ok, looks like the long way it is then!

Thanks a lot for your help everyone!