# Concatenating cells to produce a cell range text string that can then be summed

1. ## Concatenating cells to produce a cell range text string that can then be summed

Hello. I'm a casual user with Microsoft 365, and this is strictly for my own personal leisure use.

I have 3 individual cells for manual input, 1 letter and two numerals, and then 1 cell for output, and one cell for final result. I'm concatenating the first three, to produce a string in the 4th cell that represents a cell range.

I'll be using the input cells for a column, a start row, and an end row, and I have a concat formula in the output cell. Then I have several columns of numerals in columns A, B, and C from rows 101 to 500.

By using the following formula: =+concat(a1, b1, ":", a1, c1)

I'm able to produce the text string that represents my desired cell range.

For instance, if I input the letter a into A1, the number 101 into B1, and the number 500 into C1, I get the following text string a101:a500
If I input C, 230, and 489 into the three cells, it will then produce the text string c230:c489

Then I want to take that text string from the output cell, and do a sum on the cell range that it represents, and that's where I'm getting stuck. It has to be something easy that I'm missing. The cell range needs to be a value, in order to use the sum formula, right? So I guess all I should need to do is to convert the text string I generated into a value. But I'm at a loss on that one.

Thanks in advance for any insight anyone has for this.

2. ## Re: Concatenating cells to produce a cell range text string that can then be summed

Try this

=SUM(INDIRECT(CONCATENATE(A1,B1,":",A1,C1)))

3. ## Re: Concatenating cells to produce a cell range text string that can then be summed

That is a winner, thank you so much. I knew I was on the right track, but I didn't have any clue about the INDIRECT function.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1