array to normal version formula

  • Thread starter Thread starter excelFan
  • Start date Start date
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
 
Hi,
try

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

if this helps please click yes, thanks
 
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
Click to expand...


Try this formula:

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

Hope this helps / Lars-Åke
 
Eduardo said:
Hi,
try

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

if this helps please click yes, thanks
Click to expand...


This formula does not work
Thanks Eduardo for your assessment
 
Lars-Ã…ke Aspelin said:
Try this formula:

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

Hope this helps / Lars-Ã…ke
Click to expand...
Also do not work. Thanks Lars-Ã…ke Aspelin. Please see if other formula can
do the job
 
Also do not work. Thanks Lars-Åke Aspelin. Please see if other formula can
do the job
Click to expand...


"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
 
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!)
Click to expand...
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 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
Click to expand...
......
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
 
.....
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
Click to expand...


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

Lars-Åke
 
Back
Top