SUMPRODUCT across all sheets

0
votes

I have more than 20 sheets in my workbook. I'm attempting to SUM all values for Month and Year.

This formula I'm trying to figure out. This is technically suppose to lookup all sheets and add the total values for specified Month, in this case January.

=SUMPRODUCT(--(MONTH('wildcard here'!B25:B48)=1),'wildcard here'!D25:D48)

or

=SUMPRODUCT(--(MONTH('wildcard here'!$B$25:$B$48)=1),'wildcard here'!$C$25:$C$48)

I can't seem to make this work. I'm getting #REF error.

2
excel
Could you clarify how your naming conventions are set up? Is MONTH the prefix for each tab? What does it reference? – Grade 'Eh' Bacon
I believe month is the function. =SUMPRODUCT(--(MONTH(Sheet1!A2:A11)=1),Sheet1!B2:B11) works for me as long as A1 to A11 has proper dates in it. Your function includes ''! which makes it look like you are entering a blank sheet name which you can't have. Also excel doesn't really handle formulas across all sheets, you are probably better having a summary sheet and doing the calculation on each sheet individually and summing the results. – gtwebb
You say "SUM all values for Month and Year", though I can't see any attempted reference to a year in your formulas? – XOR LX
Each tab has different names (clients name), this is why I wanted to capture the tabs using * (wildcard). =SUMPRODUCT(--(MONTH('wildcard here'!B25:B48)=1),'wildcard here'!D25:D48) – Grey
Is the specified month for a given year? Or would you be summing all values for e.g. January together, even if one was January 2014, another January 2015, etc.? – XOR LX

2 Answers

0
votes

Theoretically, this would work using two concepts: Array Formulas, and the INDIRECT function. A hypothetical formula might look something like this:

=SUM(INDIRECT("Sheet"&{1,2,3,4,5}&"!A1"))

In this example, the INDIRECT function is used to type in each sheet name (assuming you have Sheet1, sheet2, sheet3 etc.) and pick up A1 from all of those named sheets. However, the INDIRECT function does not allow the use of Array formulas in this way, without one additional step (see @XORLX's comment below for details):

=SUM(N(INDIRECT("Sheet"&{1,2,3,4,5}&"!A1")))

Because the sheet numbers here are hardcoded {1,2,3,4,5}, you can enter this formula as you would any other - in this way, it is not a full "array formula".

Alternate approach:

The simplest approach will be to give each sheet a column in your summary tab, and then add all those together. You can do this programmatically, so it's not quite as painful as you might think (though it will take up a lot of space).

Assume Sheet1 is blank, and along row 1 starting at column B, you have the name of each tab (I can't quite tell from your question, but let's assume something like "2010; 2011; 2012..." etc. Along column A starting at row 2, you have the month that you're looking to add. I'm also assuming that each month is set up along column A of each tab, and that your results for that month are on column B of each tab. Put together, the formula in B2 and dragged down / right would be:

=VLOOKUP($A2,INDIRECT("'"&B$1&"'!A:B"),2,0)

This will pick up the month name from column A, and check which sheet to pull it from, using the INDIRECT function. Once it has the sheet name, it adds on "A:B", meaning it will search column A using VLOOKUP. It gives you the value from the second column (column B), and needs an exact match to give the value.