Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-11-2012, 12:37 PM
sunnyside sunnyside is offline sum across columns until "0" Windows Vista sum across columns until "0" Office 2007
Novice
sum across columns until "0"
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default sum across columns until "0"

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
Reply With Quote
  #2  
Old 02-11-2012, 12:45 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sum across columns until "0" Windows XP sum across columns until "0" Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

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
Reply With Quote
  #3  
Old 02-11-2012, 04:27 PM
sunnyside sunnyside is offline sum across columns until "0" Windows Vista sum across columns until "0" Office 2007
Novice
sum across columns until "0"
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default

Thanks! I'll give it a whirl.

sunny
Reply With Quote
  #4  
Old 02-11-2012, 04:46 PM
sunnyside sunnyside is offline sum across columns until "0" Windows Vista sum across columns until "0" Office 2007
Novice
sum across columns until "0"
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default

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)))
Reply With Quote
  #5  
Old 02-11-2012, 04:48 PM
sunnyside sunnyside is offline sum across columns until "0" Windows Vista sum across columns until "0" Office 2007
Novice
sum across columns until "0"
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default I meant right to left in my last comment...

Pecoflyer,
I meant right to left in my last comment...sorry about that!
Reply With Quote
  #6  
Old 02-12-2012, 02:13 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sum across columns until "0" Windows XP sum across columns until "0" Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

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
Reply With Quote
  #7  
Old 02-12-2012, 08:31 AM
sunnyside sunnyside is offline sum across columns until "0" Windows Vista sum across columns until "0" Office 2007
Novice
sum across columns until "0"
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default RE:Left to right sum to first cell=0

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
Reply With Quote
  #8  
Old 02-12-2012, 09:16 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sum across columns until "0" Windows XP sum across columns until "0" Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

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
Reply With Quote
  #9  
Old 02-12-2012, 10:59 AM
sunnyside sunnyside is offline sum across columns until "0" Windows Vista sum across columns until "0" Office 2007
Novice
sum across columns until "0"
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default Additional detail on Left-to-Right sum until zero cell

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.
Reply With Quote
  #10  
Old 02-12-2012, 11:01 AM
sunnyside sunnyside is offline sum across columns until "0" Windows Vista sum across columns until "0" Office 2007
Novice
sum across columns until "0"
 
Join Date: Feb 2012
Posts: 9
sunnyside is on a distinguished road
Default there I go again

Right to Left....I'm dyslexic
Reply With Quote
  #11  
Old 02-12-2012, 01:02 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline sum across columns until "0" Windows XP sum across columns until "0" Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

I hope I did it right this time - See attached - Also see comments
Attached Files
File Type: xls Last Zero.xls (14.5 KB, 11 views)
__________________
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
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
sum across columns until "0" How to "Fast Forward" or "Jump into" a slide while testing 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
sum across columns until "0" "Table of content" based on "Normal Style" behavior!!!! Jamal NUMAN Word 4 07-08-2011 04:12 AM
sum across columns until "0" How to choose a "List" for certain "Heading" from "Modify" tool? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:53 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft