Adjusting a formula to collect data between two dates?

  • Thread starter Thread starter hoyos
  • Start date Start date
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.
 
Something like:
=IF(AF16="",NA(),SUMPRODUCT(--(AF16>=Date1),--(AF16<=Date2),Value)

Regards,
Fred
 
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.
 
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
 
Thanks Fred. I tried the formula but it does not count all the entries.
Always gives results one less.
Any ideas?
 
Another point. As I drag the formula down the column, all the rest are
correct. Just the first one is one down?
 
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
 
Yes your right, it was the date I had not defined it properly. its working
well with your formula......thank very much
 
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...
 
Back
Top