IF AND formula help

JC8hours

Hello,

How do I use an "IF(AND(" formula to get a count of two conditional columns? I am working on calculating a weekly average and I have a date received column which I broke down by "week number:" and "year" in separate columns. However, I have two separate Smartsheets, one being the current task and the other being the completed tasks. I need to get the average number of tasks we received over a three-year period from both Smartsheets by the week, then find the average number of tasks we completed for those respective week numbers.

So, cell link to source sheets then:

=CountIfs(And(Week Number@Row="1" from Open Task),Year=2018 Open Task, (Week Number@Row="1" from Closed Source, Year 2018 Completed Task)

Weekly Requests Totals.xlsx

I hope I explained that clearly.

To summarize: I need a rolling average of open and completed weekly task from two separate smartsheets (open and closed)

Thanks,

Mark Cronk

Your solution is along the lines of:

=COUNTIFS({open task sheet week number column}, 1, {open task sheet year column}, 2018)+ COUNTIFS({closed task sheet week number column}, 1, {closed task sheet year column}, 2018)

Help?

Mark

Mark Cronk

Your solution is along the lines of:

=COUNTIFS({open task sheet week number column}, 1, {open task sheet year column}, 2018)+ COUNTIFS({closed task sheet week number column}, 1, {closed task sheet year column}, 2018)

Help?

Mark

JC8hours

Thank you very much. This worked on my first try. 👍️

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark