Adjusting a formula to collect data between two dates?

[hoyos]

How can modify the formula below so that its criteria is between two dates?

=IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value)

Thank you.

 

[Fred Smith]

Something like:
=IF(AF16="",NA(),SUMPRODUCT(--(AF16>=Date1),--(AF16<=Date2),Value)

Regards,
Fred

 

[hoyos]

I have tried to modify your code with no joy!
The code below is your code modified to suit the file, but its not working
=SUMPRODUCT(--(Date1>=Orders!H3),--(Date1<=Orders!I3),Station=A2)

Date1= difined (that is the column with dates)
Station= defined column "Z"
Orders!H3 and I3= start and finish dates

My original formula is
=SUMPRODUCT((Orders!$B$5:$B$64988>=Orders!$H$3)*(Orders!$B$5:$B$64988<=Orders!$I$3)*(Orders!$Z$5:$Z$64988=A3))

I was after simplifying the formula by creating some dynamic ranges to use
in my formula.
I hope its a little clearer what I am tryinh to achiev.

 

[Fred Smith]

The one thing I can see is you need to turn the true/false of Station=A2
into a number. Try:
=SUMPRODUCT(--(Date1>=Orders!H3),--(Date1<=Orders!I3),--(Station=A2))

See what that does.

Regards,
Fred

 

[hoyos]

Thanks Fred. I tried the formula but it does not count all the entries.
Always gives results one less.
Any ideas?

 

[hoyos]

Another point. As I drag the formula down the column, all the rest are
correct. Just the first one is one down?

 

[Fred Smith]

Sounds to me like one of your entries is not a date. It's text masquerading
as a date. That's the first thing I'd check for.

If the formula works in some situations, but not others, then it's the data,
not the formula.

Regards,
Fred

 

[hoyos]

Yes your right, it was the date I had not defined it properly. its working
well with your formula......thank very much

 

[Fred Smith]

Glad to help. Thanks for the feedback.

Fred

Yes your right, it was the date I had not defined it properly. its working
well with your formula......thank very much