Calculations on a date column formatted as text

Question

I need to calculate a sum in one column based on a date range in another.

=SUMIFS(Data!$A:$A,Data!$B:$B,">4/1/2014")

The caveat is that the date column is being loaded as text by someone else (cannot control this). And worse, I cannot create another column that does something like

=DATEVALUE($A1)

because I can't expect this person to fill that formula down every time new data is added.

My only thought is to apply a DATEVALUE to the whole column, possibly as an array formula or similar.

Answer 1

This is possible by using the more clunky but extremely versatile SUMPRODUCT function:

=SUMPRODUCT((DATEVALUE(Data!$B:$B)>DATE(2014;1;4))*Data!$A:$A)

What it does is determine an array of all cells where DATEVALUE() > DATE(...) and then multiply this array of TRUE/FALSE items with your data.