# Calculating SUMIF across different worksheets

#### RoversFan

##### Board Regular
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?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Dave Patton

##### Well-known Member
Code:
``````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)``````

#### RoversFan

##### Board Regular
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

##### Well-known Member
"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

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
Work restrict what addins I can use.

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

Thanks a lot for your help everyone!

Replies
11
Views
457
Replies
4
Views
176
Replies
2
Views
240
Replies
3
Views
316
Replies
1
Views
364

1,191,117
Messages
5,984,747
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back