Hi,

I have 3+ sheets (say sheet1, sheet2, sheet3) all formatted the same way (with a header row):

- Column A: Date
- Column B: Amount
- Column C: Category

On a separate sheet (sheet4), I have a list of all the categories in Column A, and dates by Month/Year in row 1. I would like to sum the total amounts from sheets1-3 in sheet4 corresponding to the right month and year. I know I could sum several sumifs for each page, but I'm looking for something more efficient.

I've tried the following sumproduct formula. It works summing just one sheet (sheet1) but returns #REF if I try multiple sheets:

{=SUMPRODUCT((IF(ISERROR(MONTH(sheet1!$A:$A)),sheet1!$A:$A,MONTH(sheet1!$A:$A))=MONTH(sheet4!C$1))*(IF(ISERROR(YEAR(sheet1!$A:$A)),sheet1!$A:$A,YEAR(sheet1!$A:$A))=YEAR(sheet4!C$1))*(sheet1!$C:$C=sheet4!$A3),sheet1!$B:$B)}

This is the formula that broke (same thing as above, but multi-sheet):

{=SUMPRODUCT((IF(ISERROR(MONTH('sheet1:sheet3'!$A:$A)),'sheet1:sheet3'!$A:$A,MONTH('sheet1:sheet3'!$A:$A))=MONTH(sheet4'!C$1))*(IF(ISERROR(YEAR('sheet1:sheet3'!$A:$A)),'sheet1:sheet3'!$A:$A,YEAR('sheet1:sheet3'!$A:$A))=YEAR(sheet4!C$1))*('sheet1:sheet3'!$C:$C=sheet4!$A3),'sheet1:sheet3'!$B:$B)}

So, any ideas on how to sum multiple pages when the criteria matches a category and month/year? I am trying to avoid helper columns in sheets1-3. They are pure imported data and I'd rather not make changes to those pages. And I would like to avoid summing each page (sheet1-3) 1 at a time because there's a potential to add several more similar pages. The shorter the formula the better.

Any help is greatly appreciated. Thanks!