Sumif Format Equals

  • Thread starter Thread starter Starbuck82
  • Start date Start date
S

Starbuck82

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?
 
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
 
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
 
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
 
Back
Top