Sumif Format Equals

[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?

 

[Ashish Mathur]

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

 

[Clarity]

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

 

[Bernard Liengme]

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