# IF AND formula help

Options
✭✭

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:

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,

Tags:

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!