VBA SumIfs with Dates for comparison

[jscambray]

Hi,

I am in need of some help using SumIfs with VBA when dates are involved. Basically the variable goLiveDate is 5/1/2018 read in from cell A1 on a worksheet. dataSheet.Range("G:G") contains a range of different dates on a worksheet formatted as short date. I have tried CLng and CDate and answer still produces 0 when date comparison is added to the SumIfs. Any help appreciated.

For i = 9 To dashboardMetricsSheetLastRow
      
collectionTotal = Application.WorksheetFunction.SumIfs(dataSheet.Range("D:D"), dataSheet.Range("G:G"), "<" & Clng(goLiveDate), dataSheet.Range("H:H"), "=" & marketName, dataSheet.Range("B:B"), "=" & dashboardMetricsSheet.Range("A" & i))


Next

Thanks,

Justin

 

[James006]

Hi,

Before jumping into VBA ... have you tested your formula in your spreadsheet ?

 

[jscambray]

It does not. I still get 0. I guess I am not understanding how to make Date comparisons using SumIfs in excel. Here is the excel forumla.

=SUMIFS(Data!$D:$D, Data!$G:$G, "<" & A1, Data!$H:$H, "=" & A3,Data!$B:$B, "=" & A9)

Where Data!G:G is a column of dates formatted as Short Date, and A1 contains the date 5/1/2018 formatted as a Short Date.

If i remove the date comparison, the formula works again both in excel and VBA.

Any help appreciated.

Justin

 

[Dave Patton]

Either formula should work,
You should review your data and criteria.

Excel 2010
	A	B	C	D
1	1-May-18
2			30	30
2d

Cell Formulas
Range	Formula
C2	=SUMIFS(Data!$D:$D, Data!$G:$G, "<" & A1, Data!$H:$H, "=" & A3,Data!$B:$B, "=" & A9)
D2	=SUMIFS(Data!$D:$D, Data!$G:$G, "<" & A1, Data!$H:$H, A3,Data!$B:$B, A9)

 

[jscambray]

Thank you. I realized it was a formatting issue with the data in G:G. I had a formula assembling a data, however adding the DATEVALUE() to the individual dates fixed the issue. I tried reformatting my dates to how you have in your cell A1, and it would not reformat, indicating it was not actually reading as a date. This is fixed now.

Thank you,

Justin

 

[joeu2004]

The format of the date does not matter. But it must be a form that Excel recognizes as a date.

Confirm that =ISNUMBER(A1) and =ISNUMBER(Data!G1) for data cell in Data!G:G return TRUE.

Also, unrelated to your problem, replace whole-column ranges like $D:$D with limited ranges like $D$1:$D$10000. Replace 10000 with the max rows you truly expect to use, presumably much less than 1+ million.

Although SUMIFS might be smart enough to compare only then number of rows that have numeric data in Data!D:D, the whole-column ranges is a "bad habit" that get you into trouble with some other functions like IF() and SUMPRODUCT.

 

[jscambray]

Thank you very much for the additional advice and response. that makes sense.