Sumif Format Equals
I have both dates and currency in the same column. How do I sum the column
based on format of the cell? Specifically, how do I sumif only the cells containing currency? |
Sumif Format Equals
Hi,
You could try this. This formula would work if the currency has been formatted via Format Cells Currency In cell L14, type =CELL("format",K14) and copy down till L150. This assumes that the first entry is in cell K14. Now in any blank cell, type =SUMIF(L14:L150,"C*",K14:K150) Please note that if any entry changes in range K14:K150, you will have to go to the formula I.e. sumif and refresh by F2+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Starbuck82" wrote in message ... I have both dates and currency in the same column. How do I sum the column based on format of the cell? Specifically, how do I sumif only the cells containing currency? |
Sumif Format Equals
Hi,
You can do this by using the CELL formula in a "helper" column (worth looking CELL up in Excel Help). Assuming data is in column A, enter: =CELL("format",A1) The formula will return "C2" for currency and "D1" for date format. You can then use the sumif formula based on the codes returned. Myles "Starbuck82" wrote: I have both dates and currency in the same column. How do I sum the column based on format of the cell? Specifically, how do I sumif only the cells containing currency? |
Sumif Format Equals
Excel stores dates as serial numbers. The date 1/1/2009 has value 39814.
So if each of your money values are below that value, you could use =SUMIF(A:A,"<39814") Alternatively you could use a helper column. In B1 (or some other column far to the right, and maybe hidden) enter =CELL("format",A1), copy down the column. Cells with dates will have formats of Dn (where n is a digit) while cells formatted to show 2 decimal places will have F2, while those with currency format will have C2. Very odd: a long date such as 1 January 2010 seems to have format G (general) - I am using Excel 2010 beta To sum currency formatted cell: =SUMIF(B1:B100,"C2",A1:A100) Note: the CELL function is not volatile so if you reformat a cell the formula will not update until the worksheet is next recalculated. You can force this with F9. If the helper column is not acceptable, maybe someone with give you a VBA solution. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Starbuck82" wrote in message ... I have both dates and currency in the same column. How do I sum the column based on format of the cell? Specifically, how do I sumif only the cells containing currency? |
All times are GMT +1. The time now is 04:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com