![]() |
#1
|
|||
|
|||
![]()
Hi,
I'm tyring to find a way to sum a series of cells in a single row until a cell is reached who's value = 0. Example: Row1 cells (columns A thru D then E contains "0") A=1; B=3; C=7; D=9. Then a "0" is detected in cell Column E of row1 so only values A,B,C,D are summed equaling 20. There are multiple rows (35 altogether) and the "0" can show up in any column A thru U or not at all. thank you. Sunnyside |
#2
|
||||
|
||||
![]()
Hi Perhaps something like
Code:
=sum(offset(A1,,,,match(0,a1:a100,0)))
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
![]()
Thanks! I'll give it a whirl.
sunny |
#4
|
|||
|
|||
![]()
Hi Pecoflyer,
I modified what you gave me slightly and it worked perfectly except I need this formula to work "left to right"...going from Column "U" towards Column "A" so it SUMS cells going from U2 towards A2 (in this example) until a cell containing value "0" is found. Can you adjust the formula below to do this? Thank you again. =SUM(OFFSET(A2,,,,MATCH(0,A2:U2,0))) |
#5
|
|||
|
|||
![]()
Pecoflyer,
I meant right to left in my last comment...sorry about that! |
#6
|
||||
|
||||
![]()
Perhaps
Code:
=SUM(a2:u2)-SUM(OFFSET(A2,,,,MATCH(0,A2:U2,0)))
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
![]()
Hi Pecoflyer,
This was very close: =SUM(a2:u2)-SUM(OFFSET(A2,,,,MATCH(0,A2:U2,0))). But it adds cells together to the first cell="0" from Right-to-Left + Left-to-Right. I was looking for only the Right-to-Left if you can modify. thank you again for your diligence. sunny |
#8
|
||||
|
||||
![]()
Sorry, I wrongly assumed there would only be one 0 in the row
Is this better ? Code:
=SUM(OFFSET(U2,,,,-MATCH(0,a2:u2,0)))
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
|||
|
|||
![]()
Hi Pecoflyer,
Here's the string to assist. Sorry I didn't provide this detail earlier: Assume a single row with columns A1 thru U1. 10, 12, 1, 2, 0, 3, 6, 7, 18, 20, 0, 3, 4, 30, 10, 12, 11, 0, 2, 1, 7 A1 = 10 and U1 = 7. The formular should work Left-to-Right starting with U1 and, in this case the answer would be 7+1+2 = 10. Regards. |
#10
|
|||
|
|||
![]()
Right to Left....I'm dyslexic
|
#11
|
||||
|
||||
![]()
I hope I did it right this time - See attached - Also see comments
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
George Daly | PowerPoint | 1 | 10-14-2011 06:49 AM |
How to edit the "Format" and the "show level" of an EXISTING table of content? | Jamal NUMAN | Word | 2 | 08-14-2011 10:46 AM |
![]() |
Jamal NUMAN | Word | 4 | 07-08-2011 04:12 AM |
![]() |
Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |
"Microsoft Excel Application" missing in the "Component Services" on win08 | sword.fish | Excel | 0 | 02-26-2010 02:09 PM |