Help on creating a metrics formula in smartsheet with a date range and text crteria (2 column ranges
Could someone help me with my formula below, I am not sure what I am doing wrong...
=COUNTIFS({Progress Startup Column}, "Backlog", {FPI Column}, AND({KS_Start-Up Report Range 1} >= DATE(2024, 4, 1), {KS_Start-Up Report Range 1} <= DATE(2024, 4, 30)))
Answers
-
Without seeing the sheet it's hard to know for sure but my best guess is this part here: {FPI Column}, AND({KS_Start-Up Report Range 1} >= DATE(2024, 4, 1), {KS_Start-Up Report Range 1} <= DATE(2024, 4, 30)))
It looks like FPI Column is a different column then what you are referencing in your logical statement for the AND() section.
Basically, it looks like you are saying, look at FPI Column but then you criteria is saying look at this coulmn instead.
What criteria doing you want from the FPI column? Ar you looking for the Month of April in there? If so, why reference the KS Start up Range? You don't need an AND at all in here, just write out the range and the criteria.
Try rewriting it like this: =COUNTIFS({Progress Startup Column}, "Backlog", {KS_Start-Up Report Range 1}, >= DATE(2024, 4, 1), {KS_Start-Up Report Range 1}, <= DATE(2024, 4, 30)) [Double check the paranthesis are correct]
or like this maybe?: =COUNTIFS({Progress Startup Column}, "Backlog", {FPI Column}, >= DATE(2024, 4, 1), {FPI Column}, <= DATE(2024, 4, 30)) [Double check the paranthesis are correct]
-
The KS_Start-Up Report Range 1 is actually a cell reference to the FPI column (sorry for not having that clarified originally)
-
But I also need some criteria for a second column Progress Startup Column. I would like that to be a criteria as well. So in that column if it says backlog then the FPI date within the range would be applicable if that makes sense?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!