SUMPRODUCT with cell reference rather than text

[Long Nose]

Hi all,

Can you help me turn this formula from an Array into Sumproduct?

Current working formula

{=SUM(IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUM(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2:F$188))+SUM(IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUM(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207'!F$2:F$188))+SUM(IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUM(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6),'AS21'!F$2:F$188))}

Failed to convert using

=SUMPRODUCT(--((IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2:F$188))+SUMPRODUCT(--IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207'!F$2:F$188))+SUMPRODUCT(--((IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6),'AS21'!F$2:F$188)))))))))

A6 is a month column and looks like this "Jan-08"
the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.

 

[angiemeh]

Try this:

=SUMPRODUCT(--(Month('3107'!$A$2:$A$188)=MONTH($A6)),'3107'!F$2:F$188)+SUMPRODUCT(--(MONTH('3207'!$A$2:$A$188)=MONTH($A6)),'3207'!F$2:F$188)+SUMPRODUCT(--(MONTH('4107'!$A$2:$A$188)=MONTH($A6)),'4107'!F$2:F$188)etc.....keep going you just need one ")" at the end

 

[Aladin Akyurek]

You could create on every relevant sheet an additional column, say, G, with"

=MONTH(A2)

Then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!G2:G188"),MONTH($A6),INDIRECT("'"&SheetList&"'!F2:F188")))

where SheetList refers to a range housing the relevant sheet names, that is, 3107, 3207, etc.

 

[Long Nose]

angiemeh, that does fix the problem, thanks!

Aladin, would this "sheetlist" fix the problem of "formula is too long"? Right now I have to add up three different formulas because I have too many arguments.

I'm not sure I understand all the steps in your solution.

 

[Aladin Akyurek]

angiemeh, that does fix the problem, thanks!

Aladin, would this "sheetlist" fix the problem of "formula is too long"? Right now I have to add up three different formulas because I have too many arguments.

I'm not sure I understand all the steps in your solution.

If all the sheets have the same lay-out, then the answer is yes. I'd suggest to re-read my post for the steps you need to take.

 

[Long Nose]

If all the sheets have the same lay-out, then the answer is yes. I'd suggest to re-read my post for the steps you need to take.

Aladin, Thanks for your response. I am interested in learning about this sheetlist and column G.

But, I'm failing to understand what is in the new "G" column and MONTH(A2). Can you explain a little more?

 

[Long Nose]

Wow. Aladin, It worked. This is way cool!

Thanks!

 

[Long Nose]

You could create on every relevant sheet an additional column, say, G, with"

=MONTH(A2)

Then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!G2:G188"),MONTH($A6),INDIRECT("'"&SheetList&"'!F2:F188")))

where SheetList refers to a range housing the relevant sheet names, that is, 3107, 3207, etc.

Aladin,

If I wanted to add one more criteria to this formula like MONTH($A6) and ="DDD" in column H, how is this done? My other multi-criteria formulas use the ,-- to add, but I'm stumped on this one.

Thanks a shed-load

 

[Long Nose]

Bump. Anybody?

 

[angiemeh]

What is "DDD"? I'm not 100% clear what you are trying to do.
I'll give it a shot:
Let's say you wanted to meet the criteria of both month and day. You could have your month formula in Column G. =Month(cell) then you could have a concatenated Month AND Day formula in column H

You could have a formula that concatenates the two- but put a "-" between them, so you could say =concatenate(Month(A6),"-",Day(A6))

You could replace column G with this formula if you just wanted to sum up items that only met both the day and month. If you want to do both: sum of all items that = month AND you also wanted a sum of items that = month AND Day, then put the formula in column H and copy all the way down. Then with Aladin's formula you would just use H2:H188 instead of G

I may not understand what you are asking... so if this doesn't work, please clarify.