Trouble with SUMPRODUCT & INDIRECT

[rwells]

Hello!

I have a workbook that is organized by clients' names - one sheet for each letter of the alphabet.
Each sheet is identical, and tracks sales dates and amounts.

Named range for sheets = "sheets"
Dates in column A
Amounts in column B


I am looking to sum the total sales for each month - so basically I want to so a SUMIF, but across all sheets.
Need to set this up so that it can be used in the future - it needs to recognize not only the month, but the year as well.


I've not used it before, but I think I need to use SUMPRODUCT with INDIRECT. Am having trouble with the formatting.
Any help would be appreciated.

Thanks!

 

[steve the fish]

This is how you could calculate it but it is a slow formula. See if it works ok.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!B:B"),INDIRECT("'"&Sheets&"'!A:A"),">="&B1,INDIRECT("'"&Sheets&"'!A:A"),"<"&B2))

In this example you need a date in B1 that is the start of a month and another date in B2 that is the start of the next month.

 

[rwells]

This returned a #REF ! error for me?

 

[steve the fish]

Then either in your sheets named range you have a cell that doesnt correspond to the name of one of your sheets or your are using a version of excel that doesnt have SUMIFS. Became available in 2007 version i believe.

 

[rwells]

Hallelujah! I had a space in one of my sheet names. Thanks a bunch!