Using SUMIFS() with Date criterion.

[Danielcurran]

I'm currently working in Excel 2010.

I'm using this formula
=SUMIFS(Z2:Z1000588, C2:C999999,"CONN APPLIANCES, INC", R2:R999999,">12/30/14")
and getting a #VALUE! error.

I was curious if I'm doing something wrong with the equation. I want to sum up Column Z if Column C is "CONN APPLIANCES, INC" and if Column R is later than 12/30/14.

[Ace_XL]

Your ranges are inconsistent

Use SUMPRODUCT
=SUMPRODUCT(Z2:Z999999*(C2:C999999="CONN APPLIANCES, INC")*(R2:R999999>12/30/14)

or SUMIFS
=SUMPRODUCT(Z2:Z999999*,C2:C999999,"CONN APPLIANCES, INC",R2:R999999,">"&12/30/14)

[Tony Valko]

Use SUMPRODUCT
=SUMPRODUCT(Z2:Z999999*(C2:C999999="CONN APPLIANCES, INC")*(R2:R999999>12/30/14)

That gets evaluated as 12 divided by 30 divided by 14 or 0.0285714285714286.

Better to use a cell to hold the date then refer to that cell.

[FDibbins]

Also, unless you really need the range to be that long, consider shortening it. A SP with a range that big will tend to slow your file down, is used a lot

[Danielcurran]

@Ace_XL - The formula is no longer giving an error but its including days that are less than the given date still. I tested your sumifs formula on a small set of data and it sums anywhere it sees "CONN APPLIANCES, INC" regardless of the date.

@FDibbins - Its a dynamic table that has 450,000 rows already, I doubt it would ever get to 999,999 rows but i'll take into consideration your tip of shortening the range.

[FDibbins]

Another thought...

Put your date in it's own cell, then reference it from the SUMIFS()...
=SUMIFS(Z2:Z1000588, C2:C999999,"CONN APPLIANCES, INC", R2:R999999,">"&A1)

[Danielcurran]

Yeah its odd, it works if I reference the date from another cell but it doesn't work if I type out the date, if it works it works though. Thanks for the help guys!