Use Excel SUMIF to add cells with custom number formatted dollar value

Question

I have a range of cells that contain dates and dollar amounts. I'd like to total the cells with dollars.

The worksheet is formatted in columns like this:

Date    | Amount |Date    | Amount | ... for about 20 col.
        |        |        |        |
        |        |        |        |
        |        |        |        |

I've tried various versions of SUMIF but I can't get a formula that will ignore the dates and include the dollar values. The dollar value cells have a custom number format that includes a $ sign. But I haven't been able to get anything that can use the $ to distinguish the 2 types of cells. Any suggestions?

Why is your data stacked side by side like that? It makes working with it inherently difficult. Date should be a single column and Amount should be a single column, then you can much more easily do sums or other data analysis. — tigeravatar
I inherited this from somone else. I'm trying to make an update without changing too much — OutThere

Unknown Unknown · Accepted Answer · 2016-06-16T21:39:56

The actual numerical value may be useful but it will depend upon the nature of your data (which is conspicuous by its absence).

A day is 1. A Date is 1 for every day past 31-Dec-1899. Today happens to be 42,537 and one year ago was 42,171. If no valid amount was larger than $40,000 then you can reliably use the SUMIF or SUMIFS function by discarding numerical amounts greater than EDATE(TODAY(), -12).

=sumif(a:z, "<"&edate(today(), -12), a:z)
=sumifs(a:z, a:z, "<"&edate(today(), -12))

Note the syntax differences between SUMIF and SUMIFS.

Another approach would be comparing the column header labels in a SUMPRODUCT function.

=sumproduct((a1:z1="amount")*a2:z999)

Do not use full column references with SUMPRODUCT. Always restrict your data ranges to the extents of your actual data.

Use Excel SUMIF to add cells with custom number formatted dollar value

1 Answers