IF AND formula help

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)

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:

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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.

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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!