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

Options

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

• ✭✭✭✭✭
Options

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]

• Options

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

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!