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

stonypaul

Board Regular
Joined
Jan 4, 2008
Messages
84
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
Joined
Jul 10, 2009
Messages
2,756
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
Joined
Feb 28, 2008
Messages
3,497
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
Joined
Jan 4, 2008
Messages
84
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
Joined
Feb 28, 2008
Messages
3,497
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
Joined
Jan 4, 2008
Messages
84
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
Joined
Feb 28, 2008
Messages
3,497
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
Joined
Jan 4, 2008
Messages
84
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
Joined
Jan 4, 2008
Messages
84
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))
 

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
=-SUMPRODUCT(-MID(R1:INDEX(R:R,COUNTA(R:R)),{1,2},1))

Assumes data is contiguous.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    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...
Top