How to combine formulas?
Hi all, I am trying to do some simple calculations for a project dashboard, e.g. showing the number of actions that are "not started", "in progress" (or those that are not complete or not cancelled) and that are also overdue, i.e. less than today's date. I managed to calculate the number of open actions, I also managed to calculate all actions that were due before today. But how do I combine a formula to get BOTH in one calculation?
I tried the formula below but that gives me an unparseable error.
=COUNTIFS({Actions Log Status}, <>"Complete", {Actions Log Status}, <>"Cancelled") AND (COUNTIF({Actions Log Due Date}, {Actions Log Due Date} < TODAY(0))
Any advice would be appreciated.
Best Answer
-
You need to remove one of the Due Date cross sheet references.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Wasn't sure which way you were trying to go not fully awake yet this morning go provided how to do both.
This formula will count the rows that meet all of the criteria from both formulas
=COUNTIFS({Actions Log Status}, <>"Complete", {Actions Log Status}, <>"Cancelled",{Actions Log Due Date}, {Actions Log Due Date}, < TODAY(0))
This formula will add the count of the rows that meet the first criteria and the rows that meet the criteria of the second formula.
=COUNTIFS({Actions Log Status}, <>"Complete", {Actions Log Status}, <>"Cancelled") +(COUNTIF({Actions Log Due Date}, {Actions Log Due Date} < TODAY(0))
-
Hi Hollie,
thanks for your reply. Your first formula is what I want to achieve, i.e. I want a count that meets the both the status and date criteria. However, when I try =COUNTIFS({Actions Log Status}, <>"Complete", {Actions Log Status}, <>"Cancelled",{Actions Log Due Date}, {Actions Log Due Date}, < TODAY(0)), I now get an "invalid operation" error and I have not managed to fix that.
Do you have any other suggestions? Sorry for asking again...
-
You need to remove one of the Due Date cross sheet references.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Of course! Thank you so much, Paul, now it works a treat. :-)
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!