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.
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.