| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| collect data from mail merge | Excel Worksheet Functions | |||
| collect data from one excel sheet to another | Excel Discussion (Misc queries) | |||
| Is there a way to use the P.O. template and collect/store data. | Excel Discussion (Misc queries) | |||
| How to collect data from every 60th row? | Excel Discussion (Misc queries) | |||
| collect data from different worksheet | Excel Worksheet Functions | |||
Hybrid Mode
