Sum until text

[azraeleyes]

Hi im a newbie when it comes to formulas, Is there a way to sum a column up to a certain point then it stops?

___a______b
1 _______30
2 _______40
3 _______50
4 _______32
5 text 21
6 _______67
7 _______82

I need it to sum all of column B but only until row 5 when theres a text that shows up on column A. So it would be 30+40+50+32.
I need the formula to stop wherever somebody puts text in column A.

[Glenn Kennedy]

Unless I'm mistaken (and I'm still only half awake...), this is not as easy as you might think. If your data are in A2 to A31, this will add all the numbers, until a text entry is made:

=SUM($A$2:INDEX($A$1:$A$31,IFERROR(MATCH(VLOOKUP("*",$A$2:$A$31,1,FALSE),$A$2:$A$31,0),MATCH(LOOKUP(1E+100,$A:$A),$A$1:$A$31,0))))

[Glenn Kennedy]

Simpler version:

=IFERROR(SUM($A$2:INDEX($A$1:$A$31,MATCH(VLOOKUP("*",$A$2:$A$31,1,FALSE),$A$2:$A$31,0))),SUM($A:$A))

[AliGW]

Glenn - the OP has amended their opening post and I think the sum values are in column B, with the text value in A.

[chullan88]

Hi im a newbie when it comes to formulas, Is there a way to sum a column up to a certain point then it stops?

___a______b
1 _______30
2 _______40
3 _______50
4 _______32
5 text 21
6 _______67
7 _______82

I need it to sum all of column B but only until row 5 when theres a text that shows up on column A. So it would be 30+40+50+32.
I need the formula to stop wherever somebody puts text in column A.

Hi,

Another version using OFFSET.

Please Login or Register  to view this content.

File Attached.



Regards

[AliGW]

I think it needs to be this:

=SUM(OFFSET($B$2,0,0,MATCH("*",$A$2:$A$12,0)-1))

because the row containing text should not be included.

[Glenn Kennedy]

Ali, you're right. I'm out...

[AliGW]

Ali, you're right. I'm out...

... to get another coffee?

[Glenn Kennedy]

First one of the day is whistling in the kettle right now.

[azraeleyes]

Thanks! the offset formula worked perfectly! the file attached helped tremendously as well!

[AliGW]

You're welcome!

[chullan88]

Thanks! the offset formula worked perfectly! the file attached helped tremendously as well!

Hi,

Please don't forget to add rep for all those who helped.


Regards