Application.WorksheetFunction.SumIfs beetween dates

[Nelson78]

Hello everybody.

Something is getting wrong in the following operation:

Cells(4, 5) = Application.WorksheetFunction.SumIfs(rnglav.Offset(0, 8), rnglav, "LONDON", rnglav.Offset(0, 12), "0", rnglav.Offset(0, 30), ">=" & startdate, rnglav.Offset(0, 30), "<=" & enddate)

The result is nothing (no errors, no "0").

The problem should be the dates management.

In my macro I have:

Dim startdate As Double, enddate As Double

In the column rnglav.Offset(0, 30) I have the dates in format number

rnglav.Offset(0, 30).NumberFormat = "0"

Any ideas?

 

[6StringJazzer]

The problem could be in what you posted but is more likely in your data. It is not possible to diagnose this without knowing all the values that are referenced in the formula, and how you are setting startdate and enddate and what their values are.

 

[Nelson78]

The problem could be in what you posted but is more likely in your data. It is not possible to diagnose this without knowing all the values that are referenced in the formula, and how you are setting startdate and enddate and what their values are.

Removing the part referenced to the dates, the sum is correctly performed, but it is not narrowed to the period I need.

 

[6StringJazzer]

At the point where this line of code is executed what are the values of:

rnglav.Offset(0, 30)
startdate
enddate

 

[Nelson78]

At the point where this line of code is executed what are the values of:

rnglav.Offset(0, 30)
startdate
enddate

Before the line

rnglav.Offset(0, 30).NumberFormat = "0"

the values are:

startdate 43800 Double
enddate 43830 Double

rng.offset(30) values in format date dd/mm/yyyy, example: 04/12/2019

 

[6StringJazzer]

This is difficult without access to your data. How is rnglav defined?

 

[Nelson78]

Dim lrlav As Long, rnglav As Range
        lrlav = Worksheets(1).Cells(Rows.Count, "B").End(xlUp).Row
                   Set rnglav = Worksheets(1).Range("B2:B" & lrlav)

It is defined this way.

 

[6StringJazzer]

The only thing I can think of is that either your code is looking in the wrong column for the dates, or the dates in that column are text instead of date values.

Are your dates in column AF? Are they Excel date values, not strings?

Do you have access to a site where you can post the file?