SUM based off Text in Cell

[vzwtester]

Hello,

Looking for some guidance here. I'll try to explain it the best I can.

I have the following text starting in C1 and D1: Data Volume ***. The *** indicates there is other text in the cell but I want to use Data Volume as the base and possibly a wildcard for the rest of the text that's in the text. Basically, the two cells have Data Volume in common. The rest of the text is different.


List of B Volumes

A	B	C	D	E
?	?	Data Volume ****	Data Volume ****	?
?	?	1468	3265	?
?	?	4564	56452	?

The ? marks do not have any relevance. I just put them there as fillers. Here's the catch. When I paste data into the cell, the data under Data Volume could continue for several other rows. The problem is, I don't know how much further until I actually look for the data to paste. I want to be able to past the data and automatically SUM any numbers below Data volume in columns C and D. As in SUM until Blank? No clue how to do this. I can get it to SUM using the SUMIF(C1:D1, "Data *", C2:D3) but this is me manually inserting the formula because I know after I paste, how many rows of data I actually have to SUM.

Any help would be much appreciated.

[Andrew-R]

Can you post a sample workbook? It's quite hard to follow your description. Are there more data rows below the ones you've posted, or is the sheet blank from there downwards?

[vzwtester]

Can you post a sample workbook? It's quite hard to follow your description. Are there more data rows below the ones you've posted, or is the sheet blank from there downwards?

Absolutely! They are blank until I add more data to it as I showed you in the example. That's the problem. I don't know how many rows the next data paste will take. It's almost as if, I need an always working formula in column D??

Example.xlsx

[Andrew-R]

OK, so where is the formula going? Does it have to find each row with "Data Volume" in and calculate from there downwards?

[vzwtester]

OK, so where is the formula going? Does it have to find each row with "Data Volume" in and calculate from there downwards?

The placement of the formula can be anywhere. It would need to calculate each row under "Data Volume" until there are two blanks then stop. Then I would paste more data, under those two blank rows, and it would calculate the next set of data separately. Could it calculate each row with "Data Volume" until it see blanks then stop calculating? Then, proceed to the next set of "Data Volume" inputs and calculate them?

I think I might be asking too much here LOL.

[Andrew-R]

So, like on sheet 2 of this book, then?

[vzwtester]

So, like on sheet 2 of this book, then?

That's amazing! That works perfectly.

Thank you!

[vzwtester]

Maybe I'm doing something wrong. When using the data already in the doc, it works.

When you enter new data, it adds strangely. Nevertheless, it's beyond my basic skills.

I've entered data to show how the numbers are changing when pasting data.

Copy of Example (1)-2.xlsx

[Andrew-R]

Ha, took me ages to debug that.

The formula in B3 needs to be changed to:

=IF(A3="","",SUM(OFFSET(Sheet1!$C$1,LARGE(INDEX(ROW(Sheet1!$C$1:$C$1000)*(LEFT(Sheet1!$C$1:$C$1000,4)="Data"),0),$B$1-(ROW(A1)-1)),0):OFFSET(Sheet1!$D$1,IF(ROW(A1)<$B$1,LARGE(INDEX(ROW(Sheet1!$C$1:$C$1000)*(LEFT(Sheet1!$C$1:$C$1000,4)="Data"),0),$B$1-ROW(A1)),5000)-2,0)))

And copied down.

All I've added is the "-2", right at the end of the formula, but it makes quite a big difference

[vzwtester]

Sweet...That worked! 100%

Thank you!