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

Options

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

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/05/21 Answer ✓
    Options

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

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

  • Randy Van Winkle
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    edited 02/05/21 Answer ✓
    Options

    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
    Options

    Got it! Thanks

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!