Re: [libreoffice-users] Sum of multiple cells substring

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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted