array to normal version formula

E

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
 
E

Eduardo

Hi,
try

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

if this helps please click yes, thanks
 
L

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
 
E

excelFan

Eduardo said:
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
 
E

excelFan

Lars-Ã…ke Aspelin said:
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
 
L

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
 
E

excelFan

Lars-Ã…ke Aspelin said:
"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
 
E

excelFan

excelFan said:
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
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ISODD array formula 1
Array formula - help with #NUM! errors 0
Array Formula 0
Array Formula 0
Array Formula 0
Array Formula 2
Array Formula 0
How to use vlookup in an array formula 2

Top