Need to count number of answers by quarter. Difficulty: What were you thinking?

Angela Walden

I am a bit stuck here.

I have a smartsheet (Survey Responses) that contains data from form responses from a survey. The data is synced directly from Google Forms into Smartsheet. (See below.)

I have created another smartsheet (Survey Data Points) to compile data points for metrics and reporting, which contains various formulas.

Rows 2-23 have a formula similar to the following:

=COUNTIF({Survey Responses Range 1}, "Yes")

(Range 1 is the Requirements column from Survey Responses for reference. Range 2 is the Satisfaction column. The list is pretty intuitive from there.)

Rows 24-30 have a formula similar to the following, which is based on a cell that can have more than one predefined answer selected. Hence, the use of FIND below.

=COUNTIF({Survey Responses Range 7}, FIND("Planning", @cell) > 0)

Additionally, the Survey Responses sheet (see at top) has a column labeled Timestamp, which is the date and time supplied in the following format:

7/26/2022 9:31:18

I need to expand my existing formula to include a date range for each quarter. I assume I would be using a COUNTIFS, but I've hit a brick wall on how to write this correctly. So say for Q3 of 2022, I want to find the number of "yes" answers to Requirements between 07/01/2022 and 9/30/2022 based on the date and time in the Timestamp column.

But on rows 24-30 it gets more complicated because of FIND.

The end goal is to feed all of this data up to a dashboard with a set it and forget it approach. Data comes over from the Google Form into the Smartsheet while another creates compiled data points that will be fed into a Dashboard widget.

If you are asking why I am not using a Smartsheet form to capture data, we use Google Workspace, so it is easier to capture the user info without an additional login. I am also using Zapier to automate this process, which forces my hand here a bit. Defining each column data type by anything other than Text/Number in Smartsheet will break the automation.

Any help is appreciated.

Paul Newcome

You will need to create a helper column on the source sheet to pull out that date and make it a usable format.

=DATE(VALUE(MID(Timestamp@row, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) + 1, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) - (FIND("/", Timestamp@row) + 1))))

Then in your metrics sheet you would switch over to a COUNTIFS and add another range criteria set for the dates like so:

=COUNTIFS({Survey Responses Range 7}, FIND("Planning", @cell) > 0, {New Date Column}, AND(@cell>= DATE(2022, 07, 01), @cell<= DATE(2022, 09, 30)))

Angela Walden

Using the following for the helper column:

=DATE(VALUE(MID(Timestamp@row, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) + 1, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) - (FIND("/", Timestamp@row) + 1))))

Generated the following error:

#INVALID COLUMN VALUE

However, I solved the issue by changing the Timestamp column to a "Date Created" format within Smartsheet, then unlinked the column from the source in Zapier before testing it again. Therefore, any new row will fill the date (and time) when created and it can be used like any other Date column.

Then, I used the following formula on the Survey Data Summary sheet, which I had originally attempted to use before I made my post:

=COUNTIFS({Survey Responses Range 1}, FIND("Yes", @cell) > 0, {Survey Responses Range 8}, AND(@cell >= DATE(2022, 7, 1), @cell <= DATE(2022, 9, 30)))

And Eureka! It works. :)

So for this to work, I needed a helper column (the formula you supplied did not work) or a way to capture the date in a date versus text format within Smartsheet. This is where I was stuck. Thank you so much for the help.

Paul Newcome

You will need to create a helper column on the source sheet to pull out that date and make it a usable format.

=DATE(VALUE(MID(Timestamp@row, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) + 1, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) - (FIND("/", Timestamp@row) + 1))))

Then in your metrics sheet you would switch over to a COUNTIFS and add another range criteria set for the dates like so:

=COUNTIFS({Survey Responses Range 7}, FIND("Planning", @cell) > 0, {New Date Column}, AND(@cell>= DATE(2022, 07, 01), @cell<= DATE(2022, 09, 30)))

Angela Walden

Using the following for the helper column:

=DATE(VALUE(MID(Timestamp@row, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) + 1, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("*", SUBSTITUTE(Timestamp@row, "/", "*", 2)) - (FIND("/", Timestamp@row) + 1))))

Generated the following error:

#INVALID COLUMN VALUE

However, I solved the issue by changing the Timestamp column to a "Date Created" format within Smartsheet, then unlinked the column from the source in Zapier before testing it again. Therefore, any new row will fill the date (and time) when created and it can be used like any other Date column.

Then, I used the following formula on the Survey Data Summary sheet, which I had originally attempted to use before I made my post:

=COUNTIFS({Survey Responses Range 1}, FIND("Yes", @cell) > 0, {Survey Responses Range 8}, AND(@cell >= DATE(2022, 7, 1), @cell <= DATE(2022, 9, 30)))

And Eureka! It works. :)

So for this to work, I needed a helper column (the formula you supplied did not work) or a way to capture the date in a date versus text format within Smartsheet. This is where I was stuck. Thank you so much for the help.

Paul Newcome

What causes that particular error is putting a DATE function in a non-date type column. I would be curious to know whether or not it would have worked if you had double checked your helper column was set as a date type column.

Paul Newcome

Glad you were able to find a working solution. Leveraging the system generated Created (date) type column was definitely a good thought. I wasn't sure exactly where that data was coming from, so I didn't want to suggest a change.

Angela Walden

That may have worked. But I feel my solution actually is probably simpler in the long run. However, you did put me on the right track and I appreciate that.

Our development team does post release surveys for our cards in JIRA. Our surveys are done in Google forms which feeds the results into a Google sheet. From there I use Zapier to sync the data between the Google sheet and smartsheet. Then, I built out a separate smartsheet that takes all the data from the survey and compiles data points. From there, dashboard widgets pulls the compiled data points to show how results trend between each quarter. So far it has worked flawlessly. We had a survey come in last night into Google forms and it automatically followed the complete workflow I have set up. Other than adding additional columns for each quarter in the future, there is nothing else needed for this to function.

This is a game changer and my boss is thrilled.

Paul Newcome

@Angela Watson You should be able to link the Google form directly to a Smartsheet to avoid Zapier all together. One less cog in the machine to break.

Angela Walden

The plug in does not work for me. Hence the use of Zapier. It throws a bunch of errors and will not authorize to sync.

Paul Newcome

Ah. That's odd it won't sync, but it definitely explains the use of Zapier.