Want to track assigned tasks within date ranges and need formula help.
I'm close I think. Am tracking total tasks per team member (ASLs) and also task that are assigned within the next 5, 5-10, and 10-30 business days. The roadmap sheets this metrics sheet pulls from shows tasks owners and start and finish dates for each task. Current formula for showing all assigned tasks, regardless of dates is:
=COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({BR - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({EMEA DE - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({EMEA FR - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({EMEA UK - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({Global - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({IN - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({JP - Campaign Roadmap Update Task Owner}, ASLs@row) + COUNTIFS({NA - Campaign Roadmap Update Task Owner}, ASLs@row)
First attempt at counting tasks within date ranges is this:
=COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5), COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({BR - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({EMEA DE - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({EMEA FR - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({EMEA UK - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({Global - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({IN - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({JP - Campaign Roadmap Update Task Owner}, [Total Tasks]@row) + COUNTIFS({NA - Campaign Roadmap Update Task Owner}, [Total Tasks]@row)))
Any help would be greatly appreciated.
Cheers - Scott
Best Answer
-
Did you update all of the cross sheet references to point at the appropriate sheets?
Answers
-
You need to combine bits from both of those formulas. The first is correct in that you need
COUNTIFS(.....) + COUNTIFS(.....)
The second is correct in that you need to add the date range/criteria set, but you need to add it to each of the COUNTIFS individually.
Basically take the syntax from the first COUNTIFS in your second formula, replicate it for each sheet, then add them all together as in the first formula.
-
Hi Paul and thanks,
Playing with this now. So are you saying to use the entire syntax from the first COUNTIFS through the day ranges: =COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5), COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, ASLs@row...
Also is that final COUNTIFS, "COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, ASLs@row" needed?
Thanks for the help.
Scott
-
My apologies. I misread your formula. You need to take the date portion of the first COUNTIFS in the second formula and put it into each of the COUNTIFS in the first formula. See the bold portions below and of course update the cross sheet references to point at the appropriate sheet for each one.
=COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({BR - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({EMEA DE - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({EMEA FR - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({EMEA UK - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({Global - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({IN - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({JP - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5))) + COUNTIFS({NA - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(), @cell <= TODAY(5)))
-
Ok that gives me a "#INCORRECT ARGUMENT" message. Hmmmm. Talking with the ProDesk team today. Will let you know the final answer.
Cheers and thanks for the help!
Scott
-
Did you update all of the cross sheet references to point at the appropriate sheets?
-
Yes and I finally figured it out. Final formula: =COUNTIFS({ANZ - Campaign Roadmap Update Task Owner}, ASLs@row, {ANZ - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({BR - Campaign Roadmap Update Task Owner}, ASLs@row, {BR - Campaign Roadmap Update Start Date}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({CN Campaign Roadmap Update Task Owner}, ASLs@row, {CN Campaign Roadmap Update Start}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({EMEA DE - Campaign Roadmap Update Task Owner}, ASLs@row, {EMEA DE - Campaign Roadmap Update Start}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({EMEA FR - Campaign Roadmap Update Task Owner}, ASLs@row, {EMEA FR - Campaign Roadmap Update Start}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({EMEA UK - Campaign Roadmap Update Task Owner}, ASLs@row, {EMEA UK - Campaign Roadmap Update Start}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({Global - Campaign Roadmap Update Task Owner}, ASLs@row, {Global - Campaign Roadmap Update Start}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({IN - Campaign Roadmap Update Task Owner}, ASLs@row, {IN - Campaign Roadmap Update Range 1}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({JP - Campaign Roadmap Update Task Owner}, ASLs@row, {JP - Campaign Roadmap Update Start}, AND(@cell >= TODAY(6), @cell <= TODAY(10))) + COUNTIFS({NA - Campaign Roadmap Update Task Owner}, ASLs@row, {NA - Campaign Roadmap Update Start}, AND(@cell >= TODAY(6), @cell <= TODAY(10)))
And yes, I now have a headache.... ;-P
-
What was the issue? I imagine probably a missing parenthesis or comma somewhere?
-
Yup. The formula was so long that I had to go section by section to find them. Thanks again for your help, Paul. Where in the world are you? I'm in Austin TX at Dell.
Cheers,
Scott
-
I was afraid that was going to get us. Ugh.
I am in the eastern panhandle of West Virginia.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!