Forum Discussion
Would Be Most Appreciative For Any Help
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
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.
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 D20Stephen
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
