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

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

"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

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

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

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

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

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

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

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

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

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

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

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

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