Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif only numbers formated as currency
If a column of data consist of currency, % and unformatted numbers, how do
you total only the data formated as currency? -- Bonita |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif only numbers formated as currency
I would insert another column and use it to indicate what's in that other column
in that same row. I'd use C for currency, % for percent, o for other (or anything you want). Then you could use: =sumif(b:b,"c",a:a) to add all the values in column A that had a C in column B. Bonita wrote: If a column of data consist of currency, % and unformatted numbers, how do you total only the data formated as currency? -- Bonita -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif only numbers formated as currency
You could use a help column, assume the values are in A1:A10, insert a new
column B and in B1 put =LEFT(CELL("format",A1))="C" copy down to B10 now use =SUMIF(B1:B10,TRUE,A1:A10) having said that, it is not a good design to use this method, all it takes is that someone changes one format by mistake and your totals will be off -- Regards, Peo Sjoblom "Bonita" wrote in message ... If a column of data consist of currency, % and unformatted numbers, how do you total only the data formated as currency? -- Bonita |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif only numbers formated as currency
Try this small UDF:
Function summ(R As Range) As Double summ = 0 For Each rr In R tx = rr.Text If Left(tx, 1) = "$" Then summ = summ + rr.Value End If Next End Function if A1 through A6 contained: 1 2 $3.00 4 $5.00 7 then =summ(a1:a6) will return 8 -- Gary''s Student - gsnu200727 "Bonita" wrote: If a column of data consist of currency, % and unformatted numbers, how do you total only the data formated as currency? -- Bonita |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding numbers to a Time Formated cell | Excel Discussion (Misc queries) | |||
Insert currency numbers from spreadsheet such as 48 = $48.00 | Excel Worksheet Functions | |||
When I type numbers in currency all I get is ####### | Excel Discussion (Misc queries) | |||
Cells formated as numbers are calculating like text | Excel Discussion (Misc queries) | |||
How do I sum currency only numbers in a range? | Excel Worksheet Functions |