Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
only total cells with $ amounts
I am trying to figiure out how to only add cells in a column that contain a
dollar amount. EXAMPLE: $12.00 $11.00 14.00 20.00 $10.00 $33.00 Total Please Help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
only total cells with $ amounts
This array* formula will do it, assuming your data is in A1:A5:
=SUM(IF(LEFT(A1:A5,1)="$",VALUE(RIGHT(A1:A5,LEN(A1 :A5)-1)),0)) Adjust the cell references to suit. *As this is an array formula, then once you have typed it in (or subsequently edit it) you must commit the formula with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete Jeffery wrote: I am trying to figiure out how to only add cells in a column that contain a dollar amount. EXAMPLE: $12.00 $11.00 14.00 20.00 $10.00 $33.00 Total Please Help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
only total cells with $ amounts
Pete... Thanks for the quick response, but I tried your formula and only get
a blank cell for result. I put the test data below in cells A1:A:5 then copied and pasted your formula into cell A:6 and comitted the formula with (CSE) the formula shows the {} brackets but the cell result is blank. Any Ideas?? Thanks "Pete_UK" wrote: This array* formula will do it, assuming your data is in A1:A5: =SUM(IF(LEFT(A1:A5,1)="$",VALUE(RIGHT(A1:A5,LEN(A1 :A5)-1)),0)) Adjust the cell references to suit. *As this is an array formula, then once you have typed it in (or subsequently edit it) you must commit the formula with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete Jeffery wrote: I am trying to figiure out how to only add cells in a column that contain a dollar amount. EXAMPLE: $12.00 $11.00 14.00 20.00 $10.00 $33.00 Total Please Help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
only total cells with $ amounts
=SUMPRODUCT(--(LEFT(A1:A5,1)="$"),--(SUBSTITUTE(A1:A5,"$","")))
The cells must be formatted as text. You can check this by selecting the cell and looking at the formula bar. If A1 ($12.00) looks like $12.00 in the formula bar, then it is formatted as text. If A1 ($12.00) looks like 12 in the formula bar, then it is formatted as currency. If you enter $12.00 in a cell without first formatting it to text, then it will format the cell as currency. To avoid this either format the cell as text before entering data, or preced the entry with an apostrophe like this '$12.00 I'm assuming you are having trouble with Pete's formula because of this issue. "Jeffery" wrote: I am trying to figiure out how to only add cells in a column that contain a dollar amount. EXAMPLE: $12.00 $11.00 14.00 20.00 $10.00 $33.00 Total Please Help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
only total cells with $ amounts
Is your $ symbol part of a text entry in the cell, or part of the
formatting? I think Pete was expecting the former, but your results suggest the latter, so you may need to use VBA. -- David Biddulph "Jeffery" wrote in message ... Pete... Thanks for the quick response, but I tried your formula and only get a blank cell for result. I put the test data below in cells A1:A:5 then copied and pasted your formula into cell A:6 and comitted the formula with (CSE) the formula shows the {} brackets but the cell result is blank. Any Ideas?? Thanks "Pete_UK" wrote: This array* formula will do it, assuming your data is in A1:A5: =SUM(IF(LEFT(A1:A5,1)="$",VALUE(RIGHT(A1:A5,LEN(A1 :A5)-1)),0)) Adjust the cell references to suit. *As this is an array formula, then once you have typed it in (or subsequently edit it) you must commit the formula with CTRL-SHIFT-ENTER (CSE) instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete Jeffery wrote: I am trying to figiure out how to only add cells in a column that contain a dollar amount. EXAMPLE: $12.00 $11.00 14.00 20.00 $10.00 $33.00 Total Please Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
total two cells from one worksheet to one cell in another workshee | Excel Discussion (Misc queries) | |||
Calculating total value based on another cell's state | Excel Discussion (Misc queries) | |||
How do I search for a total using nonadjacent cells | Excel Worksheet Functions | |||
In several cells I have =?+423.55 I want total w/o the 423.55. | Excel Worksheet Functions | |||
sumproduct to add total amounts for the month | Excel Worksheet Functions |