radioactive
New Member
- Joined
- Apr 21, 2014
- Messages
- 9
Please bear with me on this one, everything I know about excel is self-taught so my knowledge is basic at best.
I have a spreadsheet which is used as a register for certain data. There are quite a few columns, but the relevant ones for my problem contain a 'BookedDate', 'Method' and 'ReportedDate'.
I've successfully written COUNTIFS formulas (using named ranges) into a separate worksheet for calculating the number of 'methods' booked and issued on a certain date, i.e. =COUNTIFS(Method,"XXXX",BookedDate,A7) with A7 being the certain date mentioned...
However, I also need to count the number of 'methods' that were not reported within a certain timeframe (the timeframe being the days elapsed between date booked and date reported. Let's say, one day for the first method.
I have tried writing this as follows (as well as a myriad of other ways, unsuccessfully!), =(COUNTIFS(Method,"XXXX",ReportedDate,A7,ReportedDate,">=1"&BookedDate))
I know I'm missing something somewhere. Or many things. Or maybe I'm over-complicating the whole thing. Perhaps what I'm trying to do is not even possible....
But if it is, I would greatly appreciate it if somebody could point me in the right direction! It seems simple enough...
To try and explain further, if I had 5 rows of the same method (XXXX) which were all booked on the 22/04/2014, where 4 methods were reported on the 23/04/2014 and one method was not reported until the 28/04/2014, then I would expect the formula to return '1'....if it helps to know, I need to calculate this for purposes of determining a 'backlog', so to speak.
I hope this makes sense!
Many thanks in advance
I have a spreadsheet which is used as a register for certain data. There are quite a few columns, but the relevant ones for my problem contain a 'BookedDate', 'Method' and 'ReportedDate'.
I've successfully written COUNTIFS formulas (using named ranges) into a separate worksheet for calculating the number of 'methods' booked and issued on a certain date, i.e. =COUNTIFS(Method,"XXXX",BookedDate,A7) with A7 being the certain date mentioned...
However, I also need to count the number of 'methods' that were not reported within a certain timeframe (the timeframe being the days elapsed between date booked and date reported. Let's say, one day for the first method.
I have tried writing this as follows (as well as a myriad of other ways, unsuccessfully!), =(COUNTIFS(Method,"XXXX",ReportedDate,A7,ReportedDate,">=1"&BookedDate))
I know I'm missing something somewhere. Or many things. Or maybe I'm over-complicating the whole thing. Perhaps what I'm trying to do is not even possible....
But if it is, I would greatly appreciate it if somebody could point me in the right direction! It seems simple enough...
To try and explain further, if I had 5 rows of the same method (XXXX) which were all booked on the 22/04/2014, where 4 methods were reported on the 23/04/2014 and one method was not reported until the 28/04/2014, then I would expect the formula to return '1'....if it helps to know, I need to calculate this for purposes of determining a 'backlog', so to speak.
I hope this makes sense!
Many thanks in advance
