# The most efficient way to SUM() a series of substrings

#### stonypaul

##### Board Regular
If in column R I have a 6 digit numeric string in each cell like below what would be the most efficient way to calculate the total of the first two digits, then the 2nd two, then the 3rd two, other than using a lengthy formulae for each cell like these

=LEFT(R1,1) + MID(R1,2,1)
=MID(R1,3,1) + MID(R1,4,1)
=MID(R1,5,1) + MID(R1,6,1)

I have experimented with =SUMIF() but maybe I didn't get the syntax right. Thanks.

110001
011101
000000
111111
101010

#### ukmikeb

##### Well-known Member
Hi

Try :-
Code:
``````=SUM(MID(R1,1,1),MID(R1,2,1))
=SUM(MID(R1,3,1),MID(R1,4,1))
=SUM(MID(R1,5,1),MID(R1,6,1))``````
hth

Last edited:

#### Colin Legg

##### MrExcel MVP, Like totally RAD man
Your formulas look fine to me. When you say 'efficient', if you mean shorten them, then you could refactor them to:

=-SUM(-MID(R1,{1,2},1))
=-SUM(-MID(R1,{3,4},1))
=-SUM(-MID(R1,{5,6},1))

But these formulas don't offer any more efficiency in terms of calculation speed (probably fractionally slower) and they're more obscure so people are less likely to understand them.

#### stonypaul

##### Board Regular
Your formulas look fine to me. When you say 'efficient', if you mean shorten them, then you could refactor them to:

=-SUM(-MID(R1,{1,2},1))
=-SUM(-MID(R1,{3,4},1))
=-SUM(-MID(R1,{5,6},1))

But these formulas don't offer any more efficiency in terms of calculation speed (probably fractionally slower) and they're more obscure so people are less likely to understand them.
But what if I want to have a calculation for the whole column, which might have 100 cells populated with 6 digit strings (R1:R100), in one cell, then the formula becomes prohibitive

#### Colin Legg

##### MrExcel MVP, Like totally RAD man
I don't see any issue if they are all 6 digits: you'd just copy the formula down the column. Please elaborate with an actual example and I will try to help?

#### stonypaul

##### Board Regular
I don't see any issue if they are all 6 digits: you'd just copy the formula down the column. Please elaborate with an actual example and I will try to help?
Say for instance the first three cells in column R are populated, this is the formula that I'm guessing that you would propose. Obviously this works but it would get quite long so I was wondering if there was a smarter and shorter way of doing it?

Code:
``=-SUM(-MID(R1,{1,2},1),-MID(R2,{1,2},1),-MID(R3,{1,2},1))``

#### Colin Legg

##### MrExcel MVP, Like totally RAD man
If it were me I would use the individual formulas I indicated in post#3 - say in columns S,T and U, copy them down those 3 columns and then just do a simple sum of the columns to get the totals for each. It's easier to understand the formulas this way and is more efficient in calculation terms than using 3 individual formulas.

If you want to do 3 individual formulas then it would be like this:

=-SUMPRODUCT(-MID(R1:R100,{1,2},1))
=-SUMPRODUCT(-MID(R1:R100,{3,4},1))
=-SUMPRODUCT(-MID(R1:R100,{5,6},1))

#### stonypaul

##### Board Regular
If it were me I would use the individual formulas I indicated in post#3 - say in columns S,T and U, copy them down those 3 columns and then just do a simple sum of the columns to get the totals for each. It's easier to understand the formulas this way and is more efficient in calculation terms than using 3 individual formulas.

If you want to do 3 individual formulas then it would be like this:

=-SUMPRODUCT(-MID(R1:R100,{1,2},1))
=-SUMPRODUCT(-MID(R1:R100,{3,4},1))
=-SUMPRODUCT(-MID(R1:R100,{5,6},1))
Great - that works and I now have the formula below, but only the cells R3:R5 are currently populated so the formula errors. What can I replace R29 with to return the cell reference of the last populated cell in the column, rather than the value in that cell?

Code:
``=-SUMPRODUCT(-MID(R3:R29,{1,2},1))``

#### stonypaul

##### Board Regular
Great - that works and I now have the formula below, but only the cells R3:R5 are currently populated so the formula errors. What can I replace R29 with to return the cell reference of the last populated cell in the column, rather than the value in that cell?

Code:
``=-SUMPRODUCT(-MID(R3:R29,{1,2},1))``
This returns the cell address but it errors when used in the final formula

Code:
``=-SUMPRODUCT(-MID(R3:ADDRESS(MATCH(9.99999999999999E+307,R:R),COLUMN(R1)),{1,2},1))``

#### Colin Legg

##### MrExcel MVP, Like totally RAD man
=-SUMPRODUCT(-MID(R1:INDEX(R:R,COUNTA(R:R)),{1,2},1))

Assumes data is contiguous.

1,081,795
Messages
5,361,333
Members
400,627
Latest member
Mcomeaux

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
• Complex Heat Map using conditional formatting
Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
• Conditional formatting
Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...