Want to track assigned tasks within date ranges and need formula help.

Scott Shutter

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

Paul Newcome

Did you update all of the cross sheet references to point at the appropriate sheets?

Paul Newcome

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.

Scott Shutter

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

Paul Newcome

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)))

Scott Shutter

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

Paul Newcome

Did you update all of the cross sheet references to point at the appropriate sheets?

Scott Shutter

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

Paul Newcome

What was the issue? I imagine probably a missing parenthesis or comma somewhere?

Scott Shutter

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

Paul Newcome

I was afraid that was going to get us. Ugh.

I am in the eastern panhandle of West Virginia.