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.

 

[Long Nose]

Angiemeh,

DDD is a separate criteria altogether. So I'd like to sumif on month and another entirely different criteria, like company "DDD" as opposed to company "ACE." So far I have not figured out a way to add a second criteria.

 

[angiemeh]

You could still use concatenate. If in Column G you have =month(A1) etc..all the way down and then Let's say company is in column B(I don't know where it is) and you wanted to sum all the data that has the month in A6 AND the company in B6, you could put a formula in column H that says: =Concatenate(G1,"-",B1) and copy that all the way down.

Then, your formula would be

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!H2:H188"),$H$6,INDIRECT("'"&SheetList&"'!F2:F188")))

Where H already meets both criteria, so you are still looking for one criteria (but that critera has now changed to multiple criteria)

 

[Long Nose]

I see. I hadn't thought of that.

Thanks Angiemeh