Searching a letter within a range in excel

[Sparcot]

I need an excel formula that searches within a range, a cell that contains a word with letter "W" and then multiply the number stored in the cell from the right of it by 2 and display the sum of all this multiplied values in another cell.

Example: Range A4:Y4; B4 contains word "Woo" and C4 contains number "3"; E4 contains word "Wood" and F4 contains number "5"... I need Z4 to contain C4*2+E4*2+...

Please help me with that.

 

[Rick Rothstein]

Give this array-entered** formula a try...

=SUM(IFERROR(2*(LEFT(A4:X4)="W")*B4:Y4,0))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Sparcot]

Give this array-entered** formula a try...

=SUM(IFERROR(2*(LEFT(A4:X4)="W")*B4:Y4,0))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Thanks a lot! That works perfectly!

 

[Peter_SSs]

If I have understood correctly, you could also use this standard-entry formula:

=2*SUMPRODUCT(--(LEFT(A4:X4,1)="W"),B4:Y4)