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]

What if A3=Z5 and B3=Z5, etc. at the same time?

 

[brennanstephenson]

Accounting for that would be a bonus, but it's not necessary. It shouldn't be possible based on the data being entered into those cells. Assume that no two cells in the same row within the A3:E200 set will have the same contents.

 

[Aladin Akyurek]

Accounting for that would be a bonus, but it's not necessary. It shouldn't be possible based on the data being entered into those cells. Assume that no two cells in the same row within the A3:E200 set will have the same contents.

If such is not possible, we won't need a more complex formula.

Control+shift+enter, not just enter:

=SUM(IF($V$3:$AE$200=$Z5,G$3:G$200*$P$3:$P$200))/SUM(IF($V$3:$AE$200=$Z5,$P$3:$P$200))

 

[brennanstephenson]

I'm not sure this worked. Would it help to provide the actual spreadsheet?

 

[Aladin Akyurek]

I'm not sure this worked. Would it help to provide the actual spreadsheet?

What should be the range that must equal to Z5? You said: V3:V200 to A3:E200. Let's edit the formula correctly:

=SUM(IF($A$3:$E$200=$Z5,G$3:G$200*$P$3:$P$200))/SUM(IF($A$3:$E$200=$Z5,$P$3:$P$200))

confirmed with control+shift+enter, not just enter.

Does this work as intended?

 

[brennanstephenson]

I was using dummy variables to help simplify what I was asking-- Here's the exact (currently working) formula I'm trying to alter, just in case I'm not modifying your suggestion correctly:

=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)),"-=-")

Where I now need to match criteria from 5 columns-- 'January Data'!$X$3:$AB$200 instead of just one-- 'January Data'!$V$3:$V$200

 

[Aladin Akyurek]

I was using dummy variables to help simplify what I was asking-- Here's the exact (currently working) formula I'm trying to alter, just in case I'm not modifying your suggestion correctly:

=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)),"-=-")

Where I now need to match criteria from 5 columns-- 'January Data'!$X$3:$AB$200 instead of just one-- 'January Data'!$V$3:$V$200

Control+shift+enter, not just enter:

=SUM(IF('January Data'!$X$3:$AB$200=$B5,('January Data'!G$3:G$200)*('January Data'!$P$3:$P$200)))/SUM(IF('January Data'!$X$3:$AB$200=$B5,'January Data'!$P$3:$P$200))

Wrap this into an IFERROR call if needed.

 

[brennanstephenson]

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.

 

[AliGW]

This suggests that the 'match' is not what it seems: is it stored as text as opposed to a numerical value?