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

stonypaul

Board Regular
Joined
Jan 4, 2008
Messages
86
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0
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

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

Assumes data is contiguous.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top