| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
| XIRR formula in non-normal struction | Excel Worksheet Functions | |||
| Normal Text in Formula | Excel Worksheet Functions | |||
| how do i make one part of my formula bold and the other normal? | Excel Worksheet Functions | |||
| Recover earlier version of excel sheet after new version saved? | Excel Discussion (Misc queries) | |||
Linear Mode
