array to normal version formula
 

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks

array to normal version formula
 

Hi,
try

=SUMPRODUCT(--(LEFT(A1:A300)="c"), --MID(A1:A300,2,256))

if this helps please click yes, thanks

"excelFan" wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks

array to normal version formula
 

On Fri, 31 Jul 2009 03:52:03 -0700, excelFan
wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks


Try this formula:

=SUMPRODUCT((LEFT(A1:A300)="c")*(REPLACE((A1:A300) ,1,1,"")))

Hope this helps / Lars-Åke

array to normal version formula
 

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT(--(LEFT(A1:A300)="c"), --MID(A1:A300,2,256))

if this helps please click yes, thanks

"excelFan" wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks



This formula does not work
Thanks Eduardo for your assessment

array to normal version formula
 

"Lars-Ã…ke Aspelin" wrote:

On Fri, 31 Jul 2009 03:52:03 -0700, excelFan
wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks


Try this formula:

=SUMPRODUCT((LEFT(A1:A300)="c")*(REPLACE((A1:A300) ,1,1,"")))

Hope this helps / Lars-Ã…ke

Also do not work. Thanks Lars-Ã…ke Aspelin. Please see if other formula can
do the job

array to normal version formula
 

On Fri, 31 Jul 2009 05:10:01 -0700, excelFan
wrote:



"Lars-Åke Aspelin" wrote:

On Fri, 31 Jul 2009 03:52:03 -0700, excelFan
wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks


Try this formula:

=SUMPRODUCT((LEFT(A1:A300)="c")*(REPLACE((A1:A300) ,1,1,"")))

Hope this helps / Lars-Åke

Also do not work. Thanks Lars-Åke Aspelin. Please see if other formula can
do the job


"do not work" is a vague description of your problem...

What is your test data?
What result did you get with the proposed formula?
What result did you expect to get?

Lars-Åke

array to normal version formula
 

"Lars-Ã…ke Aspelin" wrote:

On Fri, 31 Jul 2009 05:10:01 -0700, excelFan
wrote:



"Lars-Ã…ke Aspelin" wrote:

On Fri, 31 Jul 2009 03:52:03 -0700, excelFan
wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks


Try this formula:

=SUMPRODUCT((LEFT(A1:A300)="c")*(REPLACE((A1:A300) ,1,1,"")))

Hope this helps / Lars-Ã…ke

Also do not work. Thanks Lars-Ã…ke Aspelin. Please see if other formula can
do the job


"do not work" is a vague description of your problem...

What is your test data?
What result did you get with the proposed formula?
What result did you expect to get?

Lars-Ã…ke

Hi again and Thanks again Lars. The formula you propose require that all cells should begin with the letter "c" , which is not true in my case (cells could be empty and cells could also have just number without leading "c"). In case of not working I get an error (#VALUE!)
The result I'm expecting is the SUM of all cells that contain NUMBERS WITH
LEADING LETTER "c". For example
A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12
......
1 c4 16 3 c9 c45 8
c17

the result will be 75 ( total of those cells which begin with "c")
Thanks again and I hope you can help me

array to normal version formula
 

"excelFan" wrote:



"Lars-Ã…ke Aspelin" wrote:

On Fri, 31 Jul 2009 05:10:01 -0700, excelFan
wrote:



"Lars-Ã…ke Aspelin" wrote:

On Fri, 31 Jul 2009 03:52:03 -0700, excelFan
wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks


Try this formula:

=SUMPRODUCT((LEFT(A1:A300)="c")*(REPLACE((A1:A300) ,1,1,"")))

Hope this helps / Lars-Ã…ke

Also do not work. Thanks Lars-Ã…ke Aspelin. Please see if other formula can
do the job


"do not work" is a vague description of your problem...

What is your test data?
What result did you get with the proposed formula?
What result did you expect to get?

Lars-Ã…ke

Hi again and Thanks again Lars. The formula you propose require that all cells should begin with the letter "c" , which is not true in my case (cells could be empty and cells could also have just number without leading "c"). In case of not working I get an error (#VALUE!)
The result I'm expecting is the SUM of all cells that contain NUMBERS WITH
LEADING LETTER "c". For example
A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12
.....
1 c4 16 3 c9 c45 8
c17

the result will be 75 ( total of those cells which begin with "c")
Thanks again and I hope you can help me

I rewrite this A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12
......
1 c4 16 3 c9 c45 8
c17


A1 1
A2 c4
A3
A4
A5 16
A6 3
A7 c9
A8
A9 c45
A10 8
A11
A12 c17

array to normal version formula
 

On Fri, 31 Jul 2009 06:15:02 -0700, excelFan
wrote:



"excelFan" wrote:



"Lars-Åke Aspelin" wrote:

On Fri, 31 Jul 2009 05:10:01 -0700, excelFan
wrote:



"Lars-Åke Aspelin" wrote:

On Fri, 31 Jul 2009 03:52:03 -0700, excelFan
wrote:

The range (Amt) from A1 to A300
may contain empty cells, numbers with leading
letter "c" like c15, c-19 and also may contain normal numbers like 1,3,88
and so on.

I'm seeking to sum ONLY amounts with the letter "c", I have an array formula
=SUM(--(IF(LEFT(A1:A300)="c",REPLACE((A1:A300),1,1,""))))
which can do the task, in my case this coding portion is a part of a bigger
formula.
So please Help how to write it in normal version
many thanks


Try this formula:

=SUMPRODUCT((LEFT(A1:A300)="c")*(REPLACE((A1:A300) ,1,1,"")))

Hope this helps / Lars-Åke

Also do not work. Thanks Lars-Åke Aspelin. Please see if other formula can
do the job


"do not work" is a vague description of your problem...

What is your test data?
What result did you get with the proposed formula?
What result did you expect to get?

Lars-Åke

Hi again and Thanks again Lars. The formula you propose require that all cells should begin with the letter "c" , which is not true in my case (cells could be empty and cells could also have just number without leading "c"). In case of not working I get an error (#VALUE!)
The result I'm expecting is the SUM of all cells that contain NUMBERS WITH
LEADING LETTER "c". For example
A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12
.....
1 c4 16 3 c9 c45 8
c17

the result will be 75 ( total of those cells which begin with "c")
Thanks again and I hope you can help me

I rewrite this A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12
.....
1 c4 16 3 c9 c45 8
c17


A1 1
A2 c4
A3
A4
A5 16
A6 3
A7 c9
A8
A9 c45
A10 8
A11
A12 c17



You already got a proposal (from Bernard Liengme) that can handle
blank cells in another thread.

Lars-Åke