Adding Cells
 

Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London

Apologies, should have stated that the formula needs to find all
entries in column A that begin with 0000013 and then add up the
corresponding cells in column M.


On Thu, 10 Mar 2005 16:58:04 +0000, Ket wrote:

Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London

Hi Ket

that makes it a bit easier :)

=SUMIF(A:A,"0000013*",M:M)

should give you what you're after

Cheers
JulieD

"Ket" wrote in message
...
Apologies, should have stated that the formula needs to find all
entries in column A that begin with 0000013 and then add up the
corresponding cells in column M.


On Thu, 10 Mar 2005 16:58:04 +0000, Ket wrote:

Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London

=SUMIF(A1:A80,"0000013*",m1:m80)

"Ket" wrote in message
...
Apologies, should have stated that the formula needs to find all
entries in column A that begin with 0000013 and then add up the
corresponding cells in column M.


On Thu, 10 Mar 2005 16:58:04 +0000, Ket wrote:

Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London

You can convert to number by doing a math operation on the cell. Easiest way
is to enter 1 in a cell, copy it (ctrl-c), select your range of text
00001345 numbers, and go to editpaste specialmultiply. (you could also
copy a 0 and use paste specialadd or subtract). This will convert them to
numbers.

=SUMPRODUCT(--(--LEFT(--A1:A8,2)=13),(M1:M8))

(based on info on your follow-up post). This would work whether or not you
convert these text to numbers with the paste special method above.. the --
before LEFT converts text to number.




"Ket" wrote in message
...
Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London

Thankyou!

On Fri, 11 Mar 2005 01:04:34 +0800, "JulieD"
wrote:

Hi Ket

that makes it a bit easier :)

=SUMIF(A:A,"0000013*",M:M)

should give you what you're after

Cheers
JulieD

"Ket" wrote in message
.. .
Apologies, should have stated that the formula needs to find all
entries in column A that begin with 0000013 and then add up the
corresponding cells in column M.


On Thu, 10 Mar 2005 16:58:04 +0000, Ket wrote:

Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London

you're welcome

"Ket" wrote in message
...
Thankyou!

On Fri, 11 Mar 2005 01:04:34 +0800, "JulieD"
wrote:

Hi Ket

that makes it a bit easier :)

=SUMIF(A:A,"0000013*",M:M)

should give you what you're after

Cheers
JulieD

"Ket" wrote in message
. ..
Apologies, should have stated that the formula needs to find all
entries in column A that begin with 0000013 and then add up the
corresponding cells in column M.


On Thu, 10 Mar 2005 16:58:04 +0000, Ket wrote:

Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London