SUM PRODUCT - Multiple Criteria

[mwildcats]

Hi All,

New member

Struggling with SUMPRODUCT / SUMIF Function.
I can do this successfully with 2 criteria / ranges but wont allow me any further.

I am wanting to sum up data in a tab/cells (Template!$AL$5:$AX$1115)
Based on numerous criteria from many drop down/ data validation lists

1st criteria range = (Template!$I$5:$I$1115) 1st criteria ('Requirement'!$C93)
2nd criteria range =(Template!$AL$4:$AX$4) 2nd criteria ('Requirement'!D$73)
3rd criteria range = (Template!$I$5:$I$1115) 3rd criteria ('Requirement'!$C94)
4th criteria range = (Template!$C$5:$C$1115) 4th criteria ('Requirement'!$C95)
5th criteria range = (Template!$F$5:$F$1115) 5th criteria ('Requirement'!$C96)
6th criteria range = (Template!$E$5:$E$1115) 6th criteria ('Requirement'!$C97)
7th criteria range = (Template!$AD$5:$AD$1115) 7th criteria ('Requirement'!$C98)
8th criteria range = (Template!$AE$5:$AE$1115) 8th criteria ('Requirement'!$C99)
9th criteria range = (Template!$AG$5:$AG$1115) 9th criteria ('Requirement'!$C100)
10th criteria range = (Template!$AH$5:$AH$1115) 10th criteria ('Requirement'!$C100)

Anyone help with whats going wrong?, provide formula or other method?

thanks,
mark

 

[tyija1995]

Re: SUM PRODUCT Help - Multiple Criteria

SUMPRODUCT((Template!$I$5:$I$1115=Requirement!$C93)*(Template!$AL$4:$AX$4=Requirement!D$73)*(Template!$I$5:$I$1115=Requirement!$C94)*(Template!$C$5:$C$1115=Requirement!$C95)*(Template!$F$5:$F$1115=Requirement!$C96)*(Template!$E$5:$E$1115=Requirement!$C97)*(Template!$AD$5:$AD$1115=Requirement!$C98)*(Template!$AE$5:$AE$1115=Requirement!$C99)*(Template!$AG$5:$AG$1115=Requirement!$C100)*(Template!$AH$5:$AH$1115=Requirement!$C100))

 

[mwildcats]

Re: SUM PRODUCT Help - Multiple Criteria

SUMPRODUCT((Template!$I$5:$I$1115=Requirement!$C93)*(Template!$AL$4:$AX$4=Requirement!D$73)*(Template!$I$5:$I$1115=Requirement!$C94)*(Template!$C$5:$C$1115=Requirement!$C95)*(Template!$F$5:$F$1115=Requirement!$C96)*(Template!$E$5:$E$1115=Requirement!$C97)*(Template!$AD$5:$AD$1115=Requirement!$C98)*(Template!$AE$5:$AE$1115=Requirement!$C99)*(Template!$AG$5:$AG$1115=Requirement!$C100)*(Template!$AH$5:$AH$1115=Requirement!$C100))

Thanks - However i cannot the see the table i wanting to add up the data from in this formula (Template!$AL$5:$AX$1115) ??
Where would this go, as the formula above is just setting the criteria i want to look up from?

 

[tyija1995]

Re: SUM PRODUCT Help - Multiple Criteria

Hmm, okay so what I sent gives the criteria for the Template!$AL$5:$AX$1115, try append that on to the end of the formula as such:

=SUMPRODUCT((Template!$I$5:$I$1115=Requirement!$C93)*(Template!$AL$4:$AX$4=Requirement!D$73)*(Template!$I$5:$I$1115=Requirement!$C94)*(Template!$C$5:$C$1115=Requirement!$C95)*(Template!$F$5:$F$1115=Requirement!$C96)*(Template!$E$5:$E$1115=Requirement!$C97)*(Template!$AD$5:$AD$1115=Requirement!$C98)*(Template!$AE$5:$AE$1115=Requirement!$C99)*(Template!$AG$5:$AG$1115=Requirement!$C100)*(Template!$AH$5:$AH$1115=Requirement!$C100)*(Template!$AL$5:$AX$1115))

If that fails I'd use a SUMIFS function with similar logic.

 

[mwildcats]

Re: SUM PRODUCT Help - Multiple Criteria

Great thanks,
Works perfectly!