Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum x cells that are greater than zero on the right?
Hi
I am finding a solution (formula) to automatically sum (3 or 12) cells on the right after encountering first right cell greater than 0. Currently, this is done manually. (eg. 3.8 is the sum of 2.9 (Aug) + 0.5 (Sep) + 0.4 (Oct).) Question is there a way to do this automatically because there are thousands of rows..and this will drive me crasy in maintaining this worksheet effectively. Thanks. First First 3M 12M Jul Aug Sep Oct Nov Dec .................... --------------------------------------------------------------------------- ---------------------------------------------------------------- 3.8 5.4 0.0 2.9 0.5 0.4 0.3 0.2 .................... 1.1 2.2 0.0 0.9 0.0 0.2 0.0 0.4 .................... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 .................... 4.1 5.2 0.0 0.0 0.0 1.9 1.3 0.9 .................... 3.1 5.1 0.0 0.0 0.0 0.5 1.7 0.9 .................... 2.6 3.9 0.0 0.0 0.0 0.4 1.1 1.1 .................... Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum x cells that are greater than zero on the right?
Hello,
I assume that your data are in columns C2 to P2. In A2 put this array formula: =IF(MIN(IF(C2:P20,COLUMN(C2:P2),""))=0,0,SUM(OFFS ET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))-1):OFFSET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))+1 ))) In B2 put this array formula: =IF(MIN(IF(C2:P20,COLUMN(C2:P2),""))=0,0,SUM(OFFS ET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))-1):OFFSET(A2,0,MIN(IF(C2:P20,COLUMN(C2:P2),""))+1 0))) Then drag down these formulas. These two formula are array formulas. You should validate these formulas with the combination of the three keystrokes CTRL+Shift+Enter instead of using the single keystroke ENTER. These formulas will be surrounded by braces. Every time you edit these formulas, you have to validate them with CTRL+Shift+Enter. Hope that will help you... Hi I am finding a solution (formula) to automatically sum (3 or 12) cells on the right after encountering first right cell greater than 0. Currently, this is done manually. (eg. 3.8 is the sum of 2.9 (Aug) + 0.5 (Sep) + 0.4 (Oct).) Question is there a way to do this automatically because there are thousands of rows..and this will drive me crasy in maintaining this worksheet effectively. Thanks. First First 3M 12M Jul Aug Sep Oct Nov Dec .................... --------------------------------------------------------------------------- ---------------------------------------------------------------- 3.8 5.4 0.0 2.9 0.5 0.4 0.3 0.2 .................... 1.1 2.2 0.0 0.9 0.0 0.2 0.0 0.4 .................... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 .................... 4.1 5.2 0.0 0.0 0.0 1.9 1.3 0.9 .................... 3.1 5.1 0.0 0.0 0.0 0.5 1.7 0.9 .................... 2.6 3.9 0.0 0.0 0.0 0.4 1.1 1.1 .................... Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum x cells that are greater than zero on the right?
On Apr 8, 12:32*pm, Learn wrote:
Hi I am finding a solution (formula) to automatically sum (3 or 12) cells on the right after encountering first right cell greater than 0. Currently, this is done manually. (eg. 3.8 is the sum of 2.9 (Aug) + 0.5 (Sep) + 0.4 (Oct).) Question is there a way to do this automatically because there are thousands of rows..and this will drive me crasy in maintaining this worksheet effectively. Thanks. First * First 3M * * 12M * * * *Jul * * Aug * * Sep * * Oct * * Nov Dec .................... --------------------------------------------------------------------------- ---------------------------------------------------------------- 3.8 * * 5.4 * * * * *0.0 * * 2.9 * * 0.5 * * 0.4 * * 0.3 0.2 .................... 1.1 * * 2.2 * * * * *0.0 * * 0.9 * * 0.0 * * 0.2 * * 0.0 0.4 .................... 0.0 * * 0.0 * * * * *0.0 * * 0.0 * * 0.0 * * 0.0 * * 0.0 0.0 .................... 4.1 * * 5.2 * * * * *0.0 * * 0.0 * * 0.0 * * 1.9 * * 1.3 0.9 .................... 3.1 * * 5.1 * * * * *0.0 * * 0.0 * * 0.0 * * 0.5 * * 1.7 0.9 .................... 2.6 * * 3.9 * * * * *0.0 * * 0.0 * * 0.0 * * 0.4 * * 1.1 1.1 .................... Thanks. Sub sumfromfirstright() For r = 2 To 7 For c = Cells(r, Columns.Count) _ ..End(xlToLeft).Column To 1 Step -1 If Cells(r, c) = 0 Then mc = c + 1 Exit For End If Next c 'MsgBox mc MsgBox Cells(r, mc) _ + Cells(r, mc + 1) _ + Cells(r, mc + 2) Next r End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum x cells that are greater than zero on the right?
On Fri, 8 Apr 2011 10:32:59 -0700 (PDT), Learn wrote:
Hi I am finding a solution (formula) to automatically sum (3 or 12) cells on the right after encountering first right cell greater than 0. Currently, this is done manually. (eg. 3.8 is the sum of 2.9 (Aug) + 0.5 (Sep) + 0.4 (Oct).) Question is there a way to do this automatically because there are thousands of rows..and this will drive me crasy in maintaining this worksheet effectively. Thanks. First First 3M 12M Jul Aug Sep Oct Nov Dec .................... --------------------------------------------------------------------------- ---------------------------------------------------------------- 3.8 5.4 0.0 2.9 0.5 0.4 0.3 0.2 .................... 1.1 2.2 0.0 0.9 0.0 0.2 0.0 0.4 .................... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 .................... 4.1 5.2 0.0 0.0 0.0 1.9 1.3 0.9 .................... 3.1 5.1 0.0 0.0 0.0 0.5 1.7 0.9 .................... 2.6 3.9 0.0 0.0 0.0 0.4 1.1 1.1 .................... Thanks. Unless the data you have posted is incomplete, I don't understand your values for the First 12M, but the following formula should work. This assumes that your actual First Month is in Column C; adjust as required: 3M: A4: =IF(COUNTIF($C4:$Z4,"0"),SUMPRODUCT(OFFSET($B4,0, MATCH(TRUE,$C4:$Z40,0),1,3)),0) 12M: B4: =IF(COUNTIF($C4:$Z4,"0"),SUMPRODUCT(OFFSET($B4,0, MATCH(TRUE,$C4:$Z40,0),1,12)),0) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum x cells that are greater than zero on the right?
This array-entered** formula appears to work for your 3M condition...
=IF(COUNTIF(C4:Z4,"0")3,SUM(IF(COLUMN(C4:Z4)<=SM ALL(IF(C4:Z4<0,COLUMN(C4:Z4)),3),C4:Z4,0)),SUM(C4 :Z4)) Change the two occurrences of the number 3 to 12 for your 12M condition. Note: You did not say what to do if there were more than 1 value but less than the number being looked for (for example, 2 non-zero value for your 3M condition)... I assumed you wanted to add what was there, hence the SUM(C4:Z4) at the end of my formula... if you want to report something else for this situation, then just change that part of the formula. **Commit formula using CTRL+SHIFT+ENTER, not just Enter by itself Rick Rothstein (MVP - Excel) "Learn" wrote in message ... Hi I am finding a solution (formula) to automatically sum (3 or 12) cells on the right after encountering first right cell greater than 0. Currently, this is done manually. (eg. 3.8 is the sum of 2.9 (Aug) + 0.5 (Sep) + 0.4 (Oct).) Question is there a way to do this automatically because there are thousands of rows..and this will drive me crasy in maintaining this worksheet effectively. Thanks. First First 3M 12M Jul Aug Sep Oct Nov Dec .................... --------------------------------------------------------------------------- ---------------------------------------------------------------- 3.8 5.4 0.0 2.9 0.5 0.4 0.3 0.2 .................... 1.1 2.2 0.0 0.9 0.0 0.2 0.0 0.4 .................... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 .................... 4.1 5.2 0.0 0.0 0.0 1.9 1.3 0.9 .................... 3.1 5.1 0.0 0.0 0.0 0.5 1.7 0.9 .................... 2.6 3.9 0.0 0.0 0.0 0.4 1.1 1.1 .................... Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with value between 2 cells and return greater value. | Excel Discussion (Misc queries) | |||
Greater of Two Cells | Excel Worksheet Functions | |||
need to add a group of cells if greater than 30 and less than 60 | Excel Discussion (Misc queries) | |||
Count non-contiguous cells where value is greater than 0 | Excel Worksheet Functions | |||
Average If Adejecent Cells Greater Than Zero | Excel Worksheet Functions |