Count number of form responses with a certain response in a 7 day period

Randy Van Winkle

II created a vehicle pre-shift checklist to log responses for a number of different vehicles at different locations. I would like to create some dashboard widgets that show how many times the form has been submitted in a 7 day period as a high level check to see that it is being utilized appropriately (and that the vehicle is actually being utilized) Not sure where to start with this one

=COUNTIFS([site column]:[site column], "vehicle name", TODAY-7)

that is probably a really bad stab at it

Kelly Moore

Answering the first question - yes the Created date will work.

In the formula above you have an extra square bracket in Aerojet. You can always tell if your column names are correct if ALL the column names become colored.

Let me know if you still have problems.

Kelly

Kelly Moore

Hey @Randy Van Winkle

I think you had a pretty good stab at what you want.

The COUNTIFS function has the syntax (range1, criteria1, range 2, criteria2, range3, criteria3, etc). You need some date range to compare against. Do they record the date? Is this a form submission and each response is a new row?

To verify- the vehicle name resides in the site column? If not, you need the vehicle column. Also, if you have the vehicle names in a list - like on a metric sheet, instead of manually inserting the vehicle name we can reference column@row as what to search for.

One solution is this (assuming your data is not being pulled f:

=COUNTIFS([site column]:[site column], "vehicle name", [date column]:[date column], AND(@cell>=TODAY(-7), @cell<TODAY(1)))

The 'AND' function is saying look back a week from today.

Try this and let me know how you need it tweaked and we'll work it until it what you like.

Kelly

Randy Van Winkle

Thanks for your response, will an automated date stamp column work for the "date column" above?

Randy Van Winkle

Still having trouble with this. Here is how I translated to my sheet

=COUNTIFS([Which Vehicle Aerojet]:[Which Vehicle Aerojet]], "ford ranger 1", [Date of Log Submission]:[Date of Log Submission], AND(@cell>=TODAY(-7), @cell<TODAY(1)))

getting #UNPARSEABLE

Best

Kelly Moore

Answering the first question - yes the Created date will work.

In the formula above you have an extra square bracket in Aerojet. You can always tell if your column names are correct if ALL the column names become colored.

Let me know if you still have problems.

Kelly

Randy Van Winkle

Got it! Thanks