Adjusting a formula to collect data between two dates?

H

hoyos

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

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

Thank you.
 
F

Fred Smith

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

Regards,
Fred
 
H

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.
 
F

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
 
H

hoyos

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

hoyos

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

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
 
H

hoyos

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

Fred Smith

Glad to help. Thanks for the feedback.

Fred

hoyos said:
Yes your right, it was the date I had not defined it properly. its working
well with your formula......thank very much
Click to expand...
 
Top