Would Be Most Appreciative For Any Help

[Stephen Coff]

I have attached a file.

I thought the SUMIFS function would be the one but only works identifying and multiplying a column. This workS for say: =SUMIFS(D15:D20,J15:J20,R14)
It doesn't work for the same logic though a tad more complex and no idea what function or how to get my result:
Like the SUMIFS above I am looking to do the same for say cell P14 though also search columns F15:20, G15:20, H15:20, I15:20 all multiplied by the corresponding numbers in column D15:20 and sum of all in P14

I hope this makes sense?
Any help is much appreciated.

Stephen

[#2]

Hi Stephen,

If i understood you correctly that could be like

=SUMPRODUCT((COUNTIF(OFFSET($E$15:$E$20,0,0,,5),$O14)*$D$15:$D$20))

in P14 (and drag it down). Please see attached.

[#3]

Sergei,
Thank you for your time. My apologies, i might not have been very clear, I hope this helps:

CELL P14 = (SUM OF CELL O15 FOUND IN ROW OF CELLS E15:I15)* CELL D15 +
                   (SUM OF CELL O15 FOUND IN ROW OF CELLS E16:I16)* CELL D16 +
                   (SUM OF CELL O15 FOUND IN ROW OF CELLS E17:I17)* CELL D17 +
                   (SUM OF CELL O15 FOUND IN ROW OF CELLS E18:I18)* CELL D18 +
                   (SUM OF CELL O15 FOUND IN ROW OF CELLS E19:I19)* CELL D19 +
                   (SUM OF CELL O15 FOUND IN ROW OF CELLS E20:I20)* CELL D20

Stephen

[#4]

Something like this though expect there must be a much simpler and neater way to do what I am trying to work out:
=SUMPRODUCT(((E15:I15=O14)*D15)+((E16:I16=O14)*D16)+((E17:I17=O14)*D17)+((E18:I18=O14)*D18)+((E19:I19=O14)*D19)+((E20:I20=O14)*D20))

Stephen

[#5]

Hi Stephen,

Sorry for misunderstanding. More compact form

=SUMPRODUCT((OFFSET($E$15:$E$20,0,0,,5)=$O14)*$D$15:$D$20)

Please see attached

[#6]

Legend, thank you.