# 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:

First attempt at counting tasks within date ranges is this:

Any help would be greatly appreciated.

Cheers - Scott

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

• 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?

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!