Formula to sum the digits
 

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

Formula to sum the digits
 

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

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

Formula to sum the digits
 

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.


"Bahareh" wrote in message
...
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

Formula to sum the digits
 

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

"Bahareh" wrote:

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

Formula to sum the digits
 

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

"Bahareh" wrote in message
...
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

Formula to sum the digits
 

On Tue, 6 Jan 2009 15:13:14 +0530, "Ashish Mathur"
wrote:

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

Formula to sum the digits
 

Point taken

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Lars-Åke Aspelin" wrote in message
...
On Tue, 6 Jan 2009 15:13:14 +0530, "Ashish Mathur"
wrote:

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

Formula to sum the digits
 

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

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

Formula to sum the digits
 

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

"Ron Rosenfeld" wrote:

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

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

Formula to sum the digits
 

On Thu, 8 Jan 2009 00:41:35 -0800, Dave Curtis
wrote:

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

Formula to sum the digits
 

"Lars-Åke Aspelin" wrote in message
...
On Tue, 6 Jan 2009 01:07:01 -0800, Bahareh
wrote:

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

Formula to sum the digits
 

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

"Lars-Åke Aspelin" wrote in message
...
On Tue, 6 Jan 2009 01:07:01 -0800, Bahareh
wrote:

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

Formula to sum the digits
 

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


On Sun, 11 Jan 2009 22:52:10 -0500, "hooroy63"
wrote:

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

"Lars-Åke Aspelin" wrote in message
.. .
On Tue, 6 Jan 2009 01:07:01 -0800, Bahareh
wrote:

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

Formula to sum the digits
 

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

"Lars-Ã…ke Aspelin" wrote:

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


On Sun, 11 Jan 2009 22:52:10 -0500, "hooroy63"
wrote:

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

"Lars-Ã…ke Aspelin" wrote in message
.. .
On Tue, 6 Jan 2009 01:07:01 -0800, Bahareh
wrote:

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

Formula to sum the digits
 

Thanks. Your formula worked perfect.

Regards
Bahareh

"Dave Curtis" wrote:

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

"Ron Rosenfeld" wrote:

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

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