In a range of cells, sum the numbers in sequence and skip the text values

Hello,

I have a range of values in a row(includes text and numbers both) Col A to Col J which would be the user input
Column M to Column V is my results table which will have the formulas

My goal is that every time i find a number in the inputs table, it should be summed at the results table, whereas if i find a text, it should just display the text. If a text shows up between the number sequence then the summation should stop and the counter should reset and the process should repeat.

For example:
Column C (M3) is 10, since this is the first number detected, (M3) at Column O should display this number itself (since first number will always be itself, but the next number will the sum of that number and this one)
User now inputs a new value 10 in Column D (M4), then the corresponding Column P (M4), should sum this value with the previous value, which is Col c+ Col D and display 20 under column P
Then again since a text value came in between, for eg Col E (M5), then this should display the text itself in Col Q (M5) and then reset the summation counter
Which means, when a new number 15 (M6) in Column F is inputted, so corresponding column R, should display that number itself (15) since the counter was reset because of the text invasion
Now M7 column G is 20 (user input), so corresponding column M7, ie Column S, should be 20 + previous number value (which is 15) so 20 +15 = 35 is displayed under column S (M7)
Now Column T, would be Col H+ Col G+ Col F (then stops because of text interruption at Column E) which is 15 +20 +5 = 40

I am unable to construct a formula for this scenario. Please help

Is this what you are looking for?

partial_sum.png

In M1:

Formula:

Please Login or Register  to view this content.

In N1:V1

Formula:

Please Login or Register  to view this content.

The attached workbook implements the above.

Hi GeoffW283,

Thank you for your prompt reply. I have a small correction to make, so sorry ! But i think you are almost there.
Instead of having a separate results tab for each column, like Col M to V, i need the results displayed on once cell, which gets updated/overriden with the new value.
So the formula should keep checking the range (Col A to Col J in your sheet) and then display one result in let say Col M, which gets overridden by the new value next based on the next month's entry.
I have updated your sheet with a working explanation

scrn.PNG

If user checks value in Jan, the green box should display value 10
if user checks value in Feb, then green box should override the old value with 10 + 10 (jan + feb) = 20
if user checks value in March, then green box should override the old value with "text" since number sequence was broken with text, then diplay that text
if user checks value in April, then green box should override the old value with value 15, as the number sequence counter was reset because of text interruption and the counting starts again
if user checks value in May then green box should override the old value with 35 (20 +15, which is may + april) ,summing the numbers until it hits a text and then it stops
if user checks value in June then green box should override the old value with 40 (20+15 + 5) summing the numbers until it hits a text and then it stops

I'll think a bit more, but right now the best solution I can come up with uses a helper row that is essentially my original solution. In the attached workbook the helper row is located at A10:J10 with essentially the same formulas as in my post #2.

The final result is in cell M7 which has the following formula. All it's doing is picking the appropriate entry from the helper row.

Formula:

Please Login or Register  to view this content.

An additional constraint is that there can be no blanks at the start of the user input area (A7:J7). For that reason I populated A7 and B7 with "x".

In your real file, how many columns maximum will the user input area have?

Hi Geoff and Dave,

Sorry if am not able to get things out in an accurate manner. Let me try to clarify a bit more here.

I have a raw data of some orders. I created a pivot table off of this raw data which will display a column for numberic $ value for the order or it will display a text (in case of special cases).
Now every month (for the next 12 months) a pivot table will have an order column each, for every month. So the Column size will increment as the months goes on ie in Jan there will be only one column for Jan, then Feb we will have 2 columns (Jan and Feb) and Dec will have 12 columns. This is columns B to M in the attached sheet.. This is what i was referring to as user input. Which is actually (technically) values coming from the orders column of the pivot table (attached sheet)
As i mentioned before there will be only one results column(in green), which is where the formula would be housed. The results column is where i need your help with
Assuming its January today, the results column will check the current value under Jan and display it under results,.
Then in Feb, the results column will check the values in the Jan and Feb column(both coming from the pivot table above) and use a formula to display the desired results as mentioned in my previous thread.

The attached sheet should solve your confusion

Please do let me know if i am still not clear.

Hi Dave,

To answer your questions:

Are we to understand that the result (target month) is to be determined by the last data entry in the row? YES
And will the "blank" cells always contain "-"? Actually the month columns will only show up at the end of the month ie in March, you will only see 3 rows for Jan, Feb and March (other month's won't show up until their turn). In May you will see Jan, Feb, March, April and May columns only and so on and so forth. I wrote the dashes to help understand the scenario better, sorry if that created a confusion
And shouldn't N13 be 35 instead of 15? You are right, i got it wrong. It should be 35

Would this be helpful to make any subtle changes to the formula above for "-" ?

hi Dave,

Just to add a point to the above thread, the results column would be at a fixed position. I will just leave blank columns for the upcoming months but yes the results column position is constant

WOW !! That worked like a charm. You are a pure genius Dave ! Thank you for all your help with all the going back and forth on this thread. I really appreciate it ! Cheers