"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