SUM below - until the next non-blank cell

[pinebush]

I'm having difficulty in writing a formula that will conditionally total values until the next non-blank cell in an adjacent column. Column 'A' contains the category labels (the individual titles do not repeat are not repeated - subsequent rows that are contained in the same category are represented as blank). Column 'B' contains the values for each line item. Column 'C' is where I want to include a formula that provides the category totals. the category totals must be at the TOP of the category, in the same row as the category labels. It is possible that Column 'B' may have blank cells, so that column cannot be used for any COUNT functions. Column 'A' seems to be the one with a consistent pattern - it either contains a label or is blank.
The user will be inserting and/or deleting rows to accommodate additional line items, and cannot be relied upon to modify a typical SUM function range. The desired function must by dynamic, as far as the number of rows to include in the sum. The screenshot and attached worksheet represent only a small portion of the actual. There are numerous spreadsheets, and numerous category labels on each. I am familiar with dynamic ranges, but cannot see that working very well in this application.
Thanks in advance for any help that you may provide,

SUM screenshot.jpg

[ChemistB]

This array formula in C2 dragged down will work (Array formulas are entered with CONTRL SHFT ENTER instead of ENTER. You'll see {} around formula if done properly)

=IFERROR(IF(ISTEXT(A2),SUM(B2:INDEX(B2:B100, MATCH(1, 1/ISTEXT(A3:A101),0))),""),SUM(B2:B100))

[bebo021999]

Try array formula in C2:

=IF(A2="","",SUM(B3:INDEX(B3:$B$17,MATCH(TRUE,A3:$A$17<>"",0),)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You could find error in last row, it can be solved, but it is not a big problem, right?

[sktneer]

In this scenario a vba code will be perfect rather than a formula. Try this code to get the desired result........

Please Login or Register  to view this content.

For detail please see the attached sheet.

[pinebush]

Thank you. That seems to work. I suspect that I can/should use absolute references when referring to the bottom of the range (e.g. SUM(B2:$B$100). I am fascinated by your formula, and am curious as to what part the IFERROR function plays.
Thanks for your prompt response.

[ChemistB]

I put in the IFERROR because, as Bebo stated, the last entry will result in an error. When this happens, the IFERROR directs it to SUM(B2:B100) which would just sum the remaining cells in B.

[acarcich]

Hello,

I know this is an old thread, but I am trying to recreate this exact scenario in OpenOffice Calc. I know there are some differences between the two programs, (for example Calc doesn't have IFERROR function), but I am trying to break down the logic so I can recreate it, and was wondering if you could explain the bold portion of the formula below:

=IFERROR(IF(ISTEXT(A2),SUM(B2:INDEX(B2:B100, MATCH(1, 1/ISTEXT(A3:A101),0))),""),SUM(B2:B100))