CODEWITHSUNDEEP
excelvba
horst
horst

Reputation: 713

WorksheetFunction.SumIfs based on Range that has date and time

I'm trying to create a SumIfs that sums all the values in a range based on a date like this

Dim v_date as Date
v_date = Date
result = WorksheetFunction.SumIfs(Range("C3:C100"),Range("A3:A100"),v_date)

The problem is that Range("A3:A100") has date and time in it but i want to filter only by date. I tried adding a wildcard like v_date & "*" but it didn't work. The result is always 0.

Upvotes: 0

Views: 50

Answers (1)

user11509084
user11509084

Reputation:

Assuming v_date is a date only it will default to midnight. Therefore you need to filter by >=v_date (midnight) and <v_date+1 (midnight next day)

result = WorksheetFunction.SumIfs(Range("C3:C100"), _
Range("A3:A100"), ">=" & CDbl(v_date), _
Range("A3:A100"), "<" & CDbl(v_date + 1))

Upvotes: 1

Related Questions