Formula to sum the digits

[Bahareh]

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4 in
some cells so there is error as ] can't be sumed. Do you know any better way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

 

[Lars-Åke Aspelin]

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4 in
some cells so there is error as ] can't be sumed. Do you know any better way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

Try this formula

=SUM(--MID(A1,ROW(OFFSET(A$2,,,FIND("]",A1)-2)),1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke

 

[Joerg Mochikun]

Would it be an option for you to use a custom format for your cell?
Something like "["#"]"

This would "optically" put brackets around your values while letting you
calculate normally with these values.

 

[Mike H]

Hi,

Extract the number with this formula

=MID(A1,2,LEN(A1)-2)

Then sum the digits with this one

=SUMPRODUCT(--MID(B1,ROW($A$1:INDEX($A:$A,LEN(B1),1)),1))

Mike

 

[Ashish Mathur]

Hi,

You can use the following array formula (Ctrl+Shift+Enter)

=SUM(1*MID(MID(B5,SEARCH("[",B5,1)+1,SEARCH("]",B5,1)-2)*1,ROW(INDIRECT("1:"&(LEN(B5)-2))),1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Lars-Åke Aspelin]

Hi,

You can use the following array formula (Ctrl+Shift+Enter)

=SUM(1*MID(MID(B5,SEARCH("[",B5,1)+1,SEARCH("]",B5,1)-2)*1,ROW(INDIRECT("1:"&(LEN(B5)-2))),1))

Your formula is either unneccessarily complicated or not complete.

The latter part, with the LEN function, assumes that there are nothing
before the "[" or after the "]".
If that is true you can simply write 2 instead of SEARCH("[",B5,1)+1
and LEN(B5)-2 instead of SEARCH("]",B5,1)-2

If you really want to allow for text before the "[" an after the "]",
like
sometext[2220]somemoretext
the formula has to be more complex. Something like

=SUM(1*MID(MID(A10,SEARCH("[",B5)+1,SEARCH("]",B5)-SEARCH("[",B5)-1),ROW(INDIRECT("1:"&(SEARCH("]",B5)-SEARCH("[",B5)-1))),1))

Lars-Åke

 

[Ashish Mathur]

Point taken

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Hi,

You can use the following array formula (Ctrl+Shift+Enter)

=SUM(1*MID(MID(B5,SEARCH("[",B5,1)+1,SEARCH("]",B5,1)-2)*1,ROW(INDIRECT("1:"&(LEN(B5)-2))),1))

Your formula is either unneccessarily complicated or not complete.

The latter part, with the LEN function, assumes that there are nothing
before the "[" or after the "]".
If that is true you can simply write 2 instead of SEARCH("[",B5,1)+1
and LEN(B5)-2 instead of SEARCH("]",B5,1)-2

If you really want to allow for text before the "[" an after the "]",
like
sometext[2220]somemoretext
the formula has to be more complex. Something like

=SUM(1*MID(MID(A10,SEARCH("[",B5)+1,SEARCH("]",B5)-SEARCH("[",B5)-1),ROW(INDIRECT("1:"&(SEARCH("]",B5)-SEARCH("[",B5)-1))),1))

Lars-Åke

 

[Ron Rosenfeld]

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4 in
some cells so there is error as ] can't be sumed. Do you know any better way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

Assuming that your square brackets are, indeed, part of the identification, and
also that the bracketed number is the only entry in the cell, then:

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("2:"&LEN(A1)-1)),1))

will sum the digits.
--ron

 

[Dave Curtis]

Hi,
Here's another way, which sums only the digits in a cell and ignores any
brackets or text characters.

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})

Dave

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4 in
some cells so there is error as ] can't be sumed. Do you know any better way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

Assuming that your square brackets are, indeed, part of the identification, and
also that the bracketed number is the only entry in the cell, then:

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("2:"&LEN(A1)-1)),1))

will sum the digits.
--ron

 

[Ron Rosenfeld]

Hi,
Here's another way, which sums only the digits in a cell and ignores any
brackets or text characters.

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})

That is very neat!
--ron

 

[hooroy63]

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4
in
some cells so there is error as ] can't be sumed. Do you know any better
way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

Try this formula

=SUM(--MID(A1,ROW(OFFSET(A$2,,,FIND("]",A1)-2)),1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke

 

[hooroy63]

Lars-Ake,
Your concise array formula works great. Please explain the minus signs at
the beginning. I've seen multiple minus signs like thie in array formulas
before, but I don't know what they do or why they're there. TIA.
Roy

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4
in
some cells so there is error as ] can't be sumed. Do you know any better
way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

Try this formula

=SUM(--MID(A1,ROW(OFFSET(A$2,,,FIND("]",A1)-2)),1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

The double minus signs are there to make the convert the output from
the MID function, that are strings, to numbers that the SUM function
likes. You can use (--)double minus, 0+ (zero plus) , or 1* (one
times) for the same pupose.
They all result in a number if it is possible to interprete the string
as a number without changing the value of the number.

Hope this helps / Lars-Åke

Lars-Ake,
Your concise array formula works great. Please explain the minus signs at
the beginning. I've seen multiple minus signs like thie in array formulas
before, but I don't know what they do or why they're there. TIA.
Roy

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4
in
some cells so there is error as ] can't be sumed. Do you know any better
way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

Try this formula

=SUM(--MID(A1,ROW(OFFSET(A$2,,,FIND("]",A1)-2)),1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke

 

[Bahareh]

Thank you Lars for your reply. I tried your formula for my Jan report. But it
gaves error of #NA. I think I couldn't undrestand inside offset which refers
to the cell after it (A$2).

Thanks for care
Bahareh

The double minus signs are there to make the convert the output from
the MID function, that are strings, to numbers that the SUM function
likes. You can use (--)double minus, 0+ (zero plus) , or 1* (one
times) for the same pupose.
They all result in a number if it is possible to interprete the string
as a number without changing the value of the number.

Hope this helps / Lars-Ã…ke

Lars-Ake,
Your concise array formula works great. Please explain the minus signs at
the beginning. I've seen multiple minus signs like thie in array formulas
before, but I don't know what they do or why they're there. TIA.
Roy

On Tue, 6 Jan 2009 01:07:01 -0800, Bahareh

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4
in
some cells so there is error as ] can't be sumed. Do you know any better
way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4


Try this formula

=SUM(--MID(A1,ROW(OFFSET(A$2,,,FIND("]",A1)-2)),1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Ã…ke

 

[Bahareh]

Thanks. Your formula worked perfect.

Regards
Bahareh

Hi,
Here's another way, which sums only the digits in a cell and ignores any
brackets or text characters.

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})

Dave

Hello

I have a work sheet of mobile sites configurations which its standars to
write is sth like [2220] or might be [224] or even [22].
Currenly to find sum of the equipmets I have to put manually 6 for [2220]
for example. How can I make it Authomatic by formula? I thought about Sum
formula combined with Left formula but as the digits might be 2 digit or 4 in
some cells so there is error as ] can't be sumed. Do you know any better way?

Exp:

[2220] =2+2+2=6
[224] =2+2+4=8
[22] =4

Assuming that your square brackets are, indeed, part of the identification, and
also that the bracketed number is the only entry in the cell, then:

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("2:"&LEN(A1)-1)),1))

will sum the digits.
--ron