array to normal version formula

[excelFan]

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

 

[Eduardo]

Hi,
try

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

if this helps please click yes, thanks

 

[Lars-Åke Aspelin]

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

 

[excelFan]

Hi,
try

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

if this helps please click yes, thanks

This formula does not work
Thanks Eduardo for your assessment

 

[excelFan]

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

 

[Lars-Åke Aspelin]

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

 

[excelFan]

"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

 

[excelFan]

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

 

[Lars-Åke Aspelin]

.....
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