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

[stonypaul]

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]

Hi

Try :-

=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

 

[Colin Legg]

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]

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]

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]

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?

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

 

[Colin Legg]

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]

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?

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

 

[stonypaul]

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?

=-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))

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

 

[Colin Legg]

=-SUMPRODUCT(-MID(R1:INDEX(R:R,COUNTA(R:R)),{1,2},1))

Assumes data is contiguous.