| Home |
| Search |
| Today's Posts |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . . . |
| 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 | |||
![Fred Smith[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
