Sum of cells where the same cell contains both text and a formula

[Coastie]

I have retired and now use google sheets instead of excel, so I hope that's not the cardinal sin of MrExcel.

I am trying to add cells that have a text label and a value, Example.

Cell A1 = 2
Cell A2 = 4
Cell A3 = 6
Cell A4 = 8

Cell B2 = ="One  "&text(A1+A2, 0)
Cell B3 = ="Two  "&text(A2+A3, 0)
Cell B4 = ="Three "&text(A3+A4, 0)

Cells B2-B4 work fine, but I would like to add those cells together, I was hoping something like this would work, but it does not.

="Total  "&text(SUM(B2:B4), 0)

Looking for a total of 30.
Is there a simple way to do that?

 

[iggydarsa]

How about
Cell B5 = ="Total "&text(A1+A2+A3+A4, 0)

 

[Coastie]

that returns a different number, than the above. Also, the B cells, are a different formula in use, I just somehow need to add the results together.

 

[iggydarsa]

Ah, stupid me

Not sure how it works in Googlesheets but in Excel I would create a helper formula on column C.
Starting on C2, I enter this formula to strip the number from the text:

=INT(MID(B2, FIND(" ", B2), 5))

Then total it at the bottom:

=SUM(C2:C4)

Not sure if it is doable in one go without a helper formula.

 

[Coastie]

that =int formula does work in googlesheets, though as you guessed, I am indeed trying to do it all in a single cell without a helper if at all possible.

 

[fjns]

Try this code

Cell A1 = 2
Cell A2 = 4
Cell A3 = 6
Cell A4 = 8

Cell B2 = ="One "&text(A1+A2, 0)
Cell B3 = ="Two "&text(A2+A3, 0)
Cell B4 = ="Three "&text(A3+A4, 0)
Cell B5 = ="Total "&text(SUM(A1:A4)+SUM(A2:A3), 0)

 

[Coastie]

Deleted...

 

[fjns]

(SUM of 2+4+6+8) = 20) + (Sum of 4+6 = 10) = 30

 

[Coastie]

that won't work because the formulas in the B cells are more complex in use. If possible, I'd need to sum the B cells.

 

[Peter_SSs]

I have retired and now use google sheets instead of excel, so I hope that's not the cardinal sin of MrExcel.

No, asking Google Sheets questions is fine - so long as they are asked in the correct forum, not the Excel Questions forum. So I have moved it for you.