ExcelBanter - Adjusting a formula to collect data between two dates?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Adjusting a formula to collect data between two dates? (https://www.excelbanter.com/excel-discussion-misc-queries/255540-adjusting-formula-collect-data-between-two-dates.html)

Hoyos

Adjusting a formula to collect data between two dates?

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[_4_]

Adjusting a formula to collect data between two dates?

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

Regards,
Fred

"hoyos" wrote in message
...
How can modify the formula below so that its criteria is between two
dates?

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

Thank you.

Hoyos

Adjusting a formula to collect data between two dates?

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)*(O rders!$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" wrote:

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

Regards,
Fred

"hoyos" wrote in message
...
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[_4_]

Adjusting a formula to collect data between two dates?

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" wrote in message
...
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)*(O rders!$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" wrote:

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

Regards,
Fred

"hoyos" wrote in message
...
How can modify the formula below so that its criteria is between two
dates?

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

Thank you.

.

Hoyos

Adjusting a formula to collect data between two dates?

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

"Fred Smith" wrote:

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" wrote in message
...
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)*(O rders!$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" wrote:

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

Regards,
Fred

"hoyos" wrote in message
...
How can modify the formula below so that its criteria is between two
dates?

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

Thank you.

.

.

Hoyos

Adjusting a formula to collect data between two dates?

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

"hoyos" wrote:

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

"Fred Smith" wrote:

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" wrote in message
...
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)*(O rders!$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" wrote:

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

Regards,
Fred

"hoyos" wrote in message
...
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[_4_]

Adjusting a formula to collect data between two dates?

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" wrote in message
...
Another point. As I drag the formula down the column, all the rest are
correct. Just the first one is one down?

"hoyos" wrote:

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

"Fred Smith" wrote:

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" wrote in message
...
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)*(O rders!$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" wrote:

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

Regards,
Fred

"hoyos" wrote in message
...
How can modify the formula below so that its criteria is between
two
dates?

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

Thank you.

.

.

Hoyos

Adjusting a formula to collect data between two dates?

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

"Fred Smith" wrote:

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" wrote in message
...
Another point. As I drag the formula down the column, all the rest are
correct. Just the first one is one down?

"hoyos" wrote:

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

"Fred Smith" wrote:

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" wrote in message
...
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)*(O rders!$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" wrote:

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

Regards,
Fred

"hoyos" wrote in message
...
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[_4_]

Adjusting a formula to collect data between two dates?

Glad to help. Thanks for the feedback.

Fred

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

"Fred Smith" wrote:

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" wrote in message
...
Another point. As I drag the formula down the column, all the rest are
correct. Just the first one is one down?

"hoyos" wrote:

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

"Fred Smith" wrote:

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" wrote in message
...
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)*(O rders!$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" wrote:

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

Regards,
Fred

"hoyos" wrote in message
...
How can modify the formula below so that its criteria is
between
two
dates?

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

Thank you.

.

.

.

All times are GMT +1. The time now is 02:38 AM.