At 17:38 04/08/2014 +0200, Daniel R. Miguel wrote:
I have a table with cells that may contain OTnum, so it might
contain OT0.5, OT1, OT2, OT2.5 et cetera. I would want to sum the
number substring of all the cells in a row, so for example if I have
this row:
Alan OT0,5 F OT1 F V OT2 F F V OT3
I would have another column with the total sum of OT:
Alan OT0,5 F OT1 F V OT2 F F V OT3 6,5
(OT3+OT2+OT1+OT0.5)
Suppose those eleven items are cells A1 to K1. In the result cell, enter
=SUM(IF(LEFT(B1:K1,2)="OT",VALUE(MID(B1:K1,3,99)),0))
But don't press Enter or click the green tick mark; instead press
Ctrl+Shift+Enter to render it an array formula. You will see that the
entire formula displayed in the Input Line has been surrounded by
braces - { } - but note that you cannot achieve the same result by
typing these yourself.
You will presumably need to copy this formula down a column. If so,
you appear to be able to achieve the correct result by copying the
formula from the first cell and pasting it into others (even
wholesale) or by using Edit | Fill, but not by dragging the fill handle.
I trust this helps.
Brian Barker