Good afternoon,
Thank you guys for your assistance. I apologize, I haven’t had time over the past week and a half or so to continue my work on this project. This may be easier if I back up and start at my overall issue, because my partial solution which would use the FIND may not the best way to go about this. I’m sure there’s a better way I haven’t yet been able to come up with, which is where I’m stuck. Here’s the details. I have two tabs in question, List! and Total Summary!. The List tab will ultimately be thousands of rows of projects. The relevant columns are in the screenshot I will attach:
E: The date the work item item was completed
F: The sprint number the work item was completed during (sprint being a 2 week timeframe)
R: The sprint number the work item was assigned during
S: The starting date of the sprint the work item was assigned
T: The ending date of the sprint the work item was assigned
U: The current status of the work item. These are figured based off other manually entered columns, and include:
- “Active - Current”: the item was assigned during the currently running sprint, and is still being worked on
- “Active - Rollover”: the item was assigned during a previous sprint but not completed, and is still active in the current sprint
- "Completed - On Time": the item was assigned and completed during the same sprint
- "Completed - Rolled Over": the item was not completed during the same sprint it was assigned, but was completed in a later sprint
- "Assigned - Future Sprint": the item has been assigned to a sprint that has not yet started
- "ENTRY ERROR": the combination of data entered in the row is not complete and the correct status can’t be determined
V: This was intended to be a helper column I was going to use to list the sprints a work item was active during. If there’s a better way to accomplish my goal, this column is not needed.
The Total Summary! Tab is a synopsis page, showing various metrics pulled from the items on List!. I’m kinda stuck on one metric, the rest of the project is done. Part of the reporting needs to show various metrics as it relates only to each specific sprint. Where I am stuck is counting the items that were active during a past sprint. I am thinking I will have to use a different method of counting depending on whether the item on List! is currently active, versus whether it has been completed in the past. The trick has been how to account for items that were active over multiple sprints, then eventually completed. They need to be counted as having been active during those sprints in between when it was assigned, and when it was completed. My thought for that piece of the puzzle was to use column V to create a list of the sprint numbers the item was active during, then use FIND in the formula on Total Summary! to determine if the sprint number being reported is on the list in column V for each work item to know if that row should be counted or not. There is a limit of 5 sprints, so the formula I currently have in column V is:
=IF($F2="","",IF($F2<=$R2,$R2, IF(($F2-$R2)=1,CONCATENATE($R2,", ",$F2), IF(($F2-$R2)=2,CONCATENATE($R2,", ",$R2+1, ", ",$F2), IF(($F2-$R2)=3,CONCATENATE($R2,", ",$R2+1, ", ",$R2+2, ", ",$F2), IF(($F2-$R2)=4,CONCATENATE($R2,", ",$R2+1, ", ",$R2+2, ", ",$R2+3,$F2), IF(($F2-$R2)=5,CONCATENATE($R2,", ",$R2+1, ", ",$R2+2, ", ",$R2+3, ", ",$R2+4, ", ",$F2),"EXCEEDED MAX")))))))
On Total Summary! (see other screenshot):
- A: the number of the sprint being reported on in that row
- B: the starting date of the sprint listed in A
- C: the ending date of the sprint listed in A
- D: new items that were assigned during the sprint number in A
- F: this is the column I’m having trouble figuring out. It needs to count the number of work items that were active during the sprint number in A
- G: will be D+F to get the total number of work items that were active during the sprint number in A
Hopefully this provides sufficient detail, if something doesn’t make sense, please let me know.
