Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum contents of multiple cells when the # of multiple cells is unk
Hello and Help! :-)
I have this formula that works just like I want it to as long as cell b8 is the only empty cell below cell b7. =IF(AND(""=B8,B8=""),SUM(C7:C8),C7) Or It could also be written like this. This seems cleaner and simpler to me. =IF(B8="",SUM(C7:C8),C7) However I need to know if there is a way to make either of the above statements work if there are multiple empty cells below cell b7 when the number of those empty cells is unknown. Keep in mind that the sum range above of C7:C8 also needs to automatically adjust to include the next row(s) down as well. Any help would be greatly appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum contents of multiple cells when the # of multiple cells is unk
Can you explain what you are trying to do?
If B8 to B10 are empty, (but B11 has data?) should we sum C7 to C10 ? The AND condition is redundant in your formula A=B is the same as B=A) so use the second one "John" wrote: Hello and Help! :-) I have this formula that works just like I want it to as long as cell b8 is the only empty cell below cell b7. =IF(AND(""=B8,B8=""),SUM(C7:C8),C7) Or It could also be written like this. This seems cleaner and simpler to me. =IF(B8="",SUM(C7:C8),C7) However I need to know if there is a way to make either of the above statements work if there are multiple empty cells below cell b7 when the number of those empty cells is unknown. Keep in mind that the sum range above of C7:C8 also needs to automatically adjust to include the next row(s) down as well. Any help would be greatly appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum contents of multiple cells when the # of multiple cells is
Yes, if B8 to B10 are empty but B11 has data then C7 to C10 should be sumed.
The catch is the number of empty cells is random in each instance. So that when I populate my spreadsheet I am having to go in and manually adjust the formula for each occurance of empty cells in column B as well as manually adjust the range that is being sumed. Any suggestions? I agree in this case the AND condition is redundant. "Toppers" wrote: Can you explain what you are trying to do? If B8 to B10 are empty, (but B11 has data?) should we sum C7 to C10 ? The AND condition is redundant in your formula A=B is the same as B=A) so use the second one "John" wrote: Hello and Help! :-) I have this formula that works just like I want it to as long as cell b8 is the only empty cell below cell b7. =IF(AND(""=B8,B8=""),SUM(C7:C8),C7) Or It could also be written like this. This seems cleaner and simpler to me. =IF(B8="",SUM(C7:C8),C7) However I need to know if there is a way to make either of the above statements work if there are multiple empty cells below cell b7 when the number of those empty cells is unknown. Keep in mind that the sum range above of C7:C8 also needs to automatically adjust to include the next row(s) down as well. Any help would be greatly appreciated |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum contents of multiple cells when the # of multiple cells is
Try:
=SUM(OFFSET($C$7,0,0,SMALL((IF($B$8:$B$1000<"",RO W(8:1000),99^99)),1)-7)) Entered with Ctrl+ShifT+Enter [CSE] (an array formula) which will have {} surrounding the formula if entered with CSE. Change the B1000/ROW(..) to suit your range. "John" wrote: Yes, if B8 to B10 are empty but B11 has data then C7 to C10 should be sumed. The catch is the number of empty cells is random in each instance. So that when I populate my spreadsheet I am having to go in and manually adjust the formula for each occurance of empty cells in column B as well as manually adjust the range that is being sumed. Any suggestions? I agree in this case the AND condition is redundant. "Toppers" wrote: Can you explain what you are trying to do? If B8 to B10 are empty, (but B11 has data?) should we sum C7 to C10 ? The AND condition is redundant in your formula A=B is the same as B=A) so use the second one "John" wrote: Hello and Help! :-) I have this formula that works just like I want it to as long as cell b8 is the only empty cell below cell b7. =IF(AND(""=B8,B8=""),SUM(C7:C8),C7) Or It could also be written like this. This seems cleaner and simpler to me. =IF(B8="",SUM(C7:C8),C7) However I need to know if there is a way to make either of the above statements work if there are multiple empty cells below cell b7 when the number of those empty cells is unknown. Keep in mind that the sum range above of C7:C8 also needs to automatically adjust to include the next row(s) down as well. Any help would be greatly appreciated |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum contents of multiple cells when the # of multiple cells is
Toppers,
Thanks for the reply, however, this still didnt work. I made some adjustments to the cell references to fit my situation still nada... I would like to send a copy of my example spreadsheet so you can really see what I am trying to do as it is kina hard to explain in words on here. I would just attach to this post if it would let me. (nospam) "Toppers" wrote: Try: =SUM(OFFSET($C$7,0,0,SMALL((IF($B$8:$B$1000<"",RO W(8:1000),99^99)),1)-7)) Entered with Ctrl+ShifT+Enter [CSE] (an array formula) which will have {} surrounding the formula if entered with CSE. Change the B1000/ROW(..) to suit your range. "John" wrote: Yes, if B8 to B10 are empty but B11 has data then C7 to C10 should be sumed. The catch is the number of empty cells is random in each instance. So that when I populate my spreadsheet I am having to go in and manually adjust the formula for each occurance of empty cells in column B as well as manually adjust the range that is being sumed. Any suggestions? I agree in this case the AND condition is redundant. "Toppers" wrote: Can you explain what you are trying to do? If B8 to B10 are empty, (but B11 has data?) should we sum C7 to C10 ? The AND condition is redundant in your formula A=B is the same as B=A) so use the second one "John" wrote: Hello and Help! :-) I have this formula that works just like I want it to as long as cell b8 is the only empty cell below cell b7. =IF(AND(""=B8,B8=""),SUM(C7:C8),C7) Or It could also be written like this. This seems cleaner and simpler to me. =IF(B8="",SUM(C7:C8),C7) However I need to know if there is a way to make either of the above statements work if there are multiple empty cells below cell b7 when the number of those empty cells is unknown. Keep in mind that the sum range above of C7:C8 also needs to automatically adjust to include the next row(s) down as well. Any help would be greatly appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 - Split Contents of Cell Across Multiple Cells | Excel Discussion (Misc queries) | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions | |||
How do I combine the contents of multiple cells in one cell? | Excel Worksheet Functions | |||
Linking contents of multiple cells to an external object | Excel Discussion (Misc queries) |