Conditional SUMPRODUCT where condition is met in any of multiple columns

[brennanstephenson]

I'm currently using the following formula for a weighted average to great success:

=SUMPRODUCT(--($V$3:$V$200=$Z5),G$3:G$200,$P$3:$P$200)/(SUMIF($V$3:$V$200,$Z5,$P$3:$P$200))

where the bold criteria is something I need to alter for another weighted average.

My problem is that the set of criteria for the new weighted average could match in any of 5 consecutive columns (matching the same Z5). I tried to change the V3:V200 to A3:E200, but SUMPRODUCT didn't seem to be a fan of that.

How can I approach this? I feel a boolean route might work, but I'm drawing circles in my head...

IF A3:A200=Z5 OR B3:B200=Z5 OR C3:C200=Z5 etc etc


Thanks for the help! I'm happy to clarify if needed!

 

[Aladin Akyurek]

No Dice. I'm getting #DIV/0 when the criteria is not met (which is consistent with other uses in the sheet, hence the IFERROR wrapping), and a #VALUE! when the criteria is met.

Does this...

=IFERROR(SUMPRODUCT(--('January Data'!$V$3:$V$200=$B5),'January Data'!G$3:G$200,'January Data'!$P$3:$P$200)/(SUMIF('January Data'!$V$3:$V$200,$B5,'January Data'!$P$3:$P$200)),"-=-")

work?