Date Formula- 60 days out

Brittanyy

Hi there, I have this formula that I am trying to create on my metrics sheet.

Goal: A formula that counts the instances where the state is not empty and is "Alabama", and where the date is before 60 days

I have a lengthy archive and want to see metrics from the last 60 days of form submission date.

Formula I have: =COUNTIFS({State}, "<>"{Alabama}, {Date}, ">", TODAY() + 60)

My references are populating for State, Alabama and Date. I keep getting an error though.

Date Column (That is referenced) is a date column: I would only want the metric to count the 3 2/14 date rows.

Andrée Starå

Hi @Brittanyy

I hope you're well and safe!

Try something like this.

=COUNTIFS({State}, <> "Alabama", {Date}, >, TODAY() + 60)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

John_Foster

Hi @Brittanyy,

I think the formula you want is below.

=COUNTIFS({State}, "<>"{Alabama}, {Date}, ">", TODAY(-60))

This should then always show you data from the last 60 days.

Hope this helps!

John

Brittanyy

Thanks @John Foster

I tried the formula, but I got a Unparseable even though its grabbing my references.

For a bit more of a context on my references:

the "State" column in our dataset allows for multiple selections, enabling users to choose more than one state. To facilitate our state count calculations, I've created helper columns for all 50 states in the referenced sheet. These helper columns populate with the respective state name if it's selected in the "State" column.

For instance, in the "Alabama" helper column, I've employed the following formula:

=IF(OR(CONTAINS("Alabama", State@row), CONTAINS("Alabama", State@row)), "Alabama", "")

This formula populates "Alabama" in the helper column if it's selected in the "State" column.

Furthermore, the "Date" column automatically records dates associated with each entry.

The original formula I used that worked: =COUNTIFS({State}, "<>", {Alabama}, "Alabama").

However, to enhance our dashboard's functionality, I'm now looking to incorporate additional logic into the formula. Specifically, I aim to count the occurrences where "Alabama" is selected in the State column and is populated in the " Alabama" column and date column is in the last 60 days

Andrée Starå

Hi @Brittanyy

I hope you're well and safe!

Try something like this.

=COUNTIFS({State}, <> "Alabama", {Date}, >, TODAY() + 60)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!