Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of cells until reach empty cell
I would like to get the sum of 7 cells which restarts when a cell containes a
zero. I want to have a result in an ajoining cell of the sum to a maximum of 7 cells in the ajoining column |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of cells until reach empty cell
In B2, enter
=IF(A2=0,SUM(INDEX(A:A,MAX(IF($A$1:A1=0,ROW($A$1:A 1)))+1):A1),"") which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and copy down. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phsdave" wrote in message ... I would like to get the sum of 7 cells which restarts when a cell containes a zero. I want to have a result in an ajoining cell of the sum to a maximum of 7 cells in the ajoining column |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of cells until reach empty cell
"phsdave" skrev i en meddelelse
... I would like to get the sum of 7 cells which restarts when a cell containes a zero. I want to have a result in an ajoining cell of the sum to a maximum of 7 cells in the ajoining column Assuming your data in A2 and down and further assuming B1 is present and empty (or containing text): In B2: =IF(A2=0,SUM($A$2:A2)-SUM($B$1:B1),"") -- Best regards Leo Heuser Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
slight variation
Bob, Im trying to use the formula you have listed to total data in column A based on markers in column B. (column A is data, column B is either 1 or 0) Id like it to start totalling column A from where there is a 1 in Column B up till it hits a 0.
There would be several sets to be summed in the column so its just from 1 to the 0 immediately above it then it restarts David Bob Phillips wrote: In B2, 28-Oct-06 In B2, enter =IF(A2=0,SUM(INDEX(A:A,MAX(IF($A$1:A1=0,ROW($A$1:A 1)))+1):A1),"") which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and copy down. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phsdave" wrote in message ... containes a Previous Posts In This Thread: On Saturday, October 28, 2006 5:17 AM phsdav wrote: sum of cells until reach empty cell I would like to get the sum of 7 cells which restarts when a cell containes a zero. I want to have a result in an ajoining cell of the sum to a maximum of 7 cells in the ajoining column On Saturday, October 28, 2006 8:19 AM Bob Phillips wrote: In B2, In B2, enter =IF(A2=0,SUM(INDEX(A:A,MAX(IF($A$1:A1=0,ROW($A$1:A 1)))+1):A1),"") which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and copy down. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phsdave" wrote in message ... containes a On Saturday, October 28, 2006 9:13 AM Leo Heuser wrote: sum of cells until reach empty cell "phsdave" skrev i en meddelelse ... Assuming your data in A2 and down and further assuming B1 is present and empty (or containing text): In B2: =IF(A2=0,SUM($A$2:A2)-SUM($B$1:B1),"") -- Best regards Leo Heuser Followup to newsgroup only please. Submitted via EggHeadCafe - Software Developer Portal of Choice HANDLING BINARY AND TEXT DATA IN XML OVER THE WIRE http://www.eggheadcafe.com/tutorials...-and-text.aspx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
slight variation
David,
Is this array formula what you need? =IF(B2=1,SUM(A2:INDEX(A:A,MAX(2,MIN(IF($B$2:B2=0,R OW($B$2:B2)+1))))),"") HTH Bob <DAVID BLAKE wrote in message ... Bob, Im trying to use the formula you have listed to total data in column A based on markers in column B. (column A is data, column B is either 1 or 0) Id like it to start totalling column A from where there is a 1 in Column B up till it hits a 0. There would be several sets to be summed in the column so its just from 1 to the 0 immediately above it then it restarts David Bob Phillips wrote: In B2, 28-Oct-06 In B2, enter =IF(A2=0,SUM(INDEX(A:A,MAX(IF($A$1:A1=0,ROW($A$1:A 1)))+1):A1),"") which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and copy down. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phsdave" wrote in message ... containes a Previous Posts In This Thread: On Saturday, October 28, 2006 5:17 AM phsdav wrote: sum of cells until reach empty cell I would like to get the sum of 7 cells which restarts when a cell containes a zero. I want to have a result in an ajoining cell of the sum to a maximum of 7 cells in the ajoining column On Saturday, October 28, 2006 8:19 AM Bob Phillips wrote: In B2, In B2, enter =IF(A2=0,SUM(INDEX(A:A,MAX(IF($A$1:A1=0,ROW($A$1:A 1)))+1):A1),"") which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and copy down. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phsdave" wrote in message ... containes a On Saturday, October 28, 2006 9:13 AM Leo Heuser wrote: sum of cells until reach empty cell "phsdave" skrev i en meddelelse ... Assuming your data in A2 and down and further assuming B1 is present and empty (or containing text): In B2: =IF(A2=0,SUM($A$2:A2)-SUM($B$1:B1),"") -- Best regards Leo Heuser Followup to newsgroup only please. Submitted via EggHeadCafe - Software Developer Portal of Choice HANDLING BINARY AND TEXT DATA IN XML OVER THE WIRE http://www.eggheadcafe.com/tutorials...-and-text.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating only non-empty cells... | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Transfer Cell Formatting for linked cells | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |