Help on creating a metrics formula in smartsheet with a date range and text crteria (2 column ranges

sillerk12

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)))

Dakota Haeffner

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]

sillerk12

The KS_Start-Up Report Range 1 is actually a cell reference to the FPI column (sorry for not having that clarified originally)

sillerk12

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?