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,
Best 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
-
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. 👍️
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!