Formula to show number of requests entered each week

Taylor Riley

Hi!

I have created a form that marketers will use to submit requests. We want to track the number of requests that we are receiving each week. Is there a function I can use in the summary data to capture this? Or is there a way to run this as a weekly report?

Taylor Riley

I also forgot to mention, I'm hoping to capture this information on a Dashboard as well.

David Joyeuse

Hi @Taylor Riley,

Since you are using a form to submit request, you have a sheet where they are all enter.

Make sure you have the automated column "Date created" on it.

In your summary data you can use this formula;

=COUNTIFS([Main Column]:[Main Column],NOT(ISBLANK(@cell)),[Date created]:[Date created], WEEKNUMBER(@cell)=WEEKNUMBER(TODAY()))

You can then use a sheet summary report to grab the info, or a metric on a dashboard to just display that info without having to go on the sheet page each time.

Taylor Riley

That worked! Thank you!

Is there a way to keep the historical data? Ideally I would like to have a graph on the dashboard that shows the number of requests each week

David Joyeuse

I would say yes.

But I believe you would have to do this manually and creates 52 cells with the same formula (preferably in the same column).

Then in the formula above replace WEEKNUMBER(TODAY()) by 1 to 52 each time.

It's a bit boring to do, but shouldn't take that long either.

Then you can select the column and make a graph out of it.

Taylor Riley

I'm not sure I follow.

Let me know if I'm getting this correctly:

1. Create a new column in my sheet
2. Use the same formula provided above
3. Each week, update the Summary Sheet formula to reflect what the current week is: =COUNTIFS([Switch Offer Reason]:[Switch Offer Reason], NOT(ISBLANK(@cell)), [Date Submitted]:[Date Submitted], WEEKNUMBER(@cell) = WEEKNUMBER(32))

I don't think I'm updating the week number correctly as I'm getting #invalid data type message

David Joyeuse

Nope that would be in the column:

• Row 1: =COUNTIFS([Switch Offer Reason]:[Switch Offer Reason], NOT(ISBLANK(@cell)), [Date Submitted]:[Date Submitted], WEEKNUMBER(@cell) = 1)
• Row 2: =COUNTIFS([Switch Offer Reason]:[Switch Offer Reason], NOT(ISBLANK(@cell)), [Date Submitted]:[Date Submitted], WEEKNUMBER(@cell) = 2)
• Row 3: =COUNTIFS([Switch Offer Reason]:[Switch Offer Reason], NOT(ISBLANK(@cell)), [Date Submitted]:[Date Submitted], WEEKNUMBER(@cell) = 3)
• And so on... until
• Row 52: =COUNTIFS([Switch Offer Reason]:[Switch Offer Reason], NOT(ISBLANK(@cell)), [Date Submitted]:[Date Submitted], WEEKNUMBER(@cell) = 52)

Taylor Riley

That worked! Thank you so much!

Paul Newcome

You could also create a separate sheet and set up a copy row automation to copy the row at the end of each week. This way you can very easily go beyond a single year.

You also do not need the first range/criteria set in the COUNTIFS because the only way the Created Date will be populated is through the form submission.

=COUNTIFS([Date Submitted]:[Date Submitted], WEEKNUMBER(@cell) = TODAY())

jennbark

Good Morning - I tried both of the formulas in these responses and get #UNPARSABLE or #INVALID OPERATION errors. I am trying to just count the number of requests submitted by a form this week (sheet has the auto fill field for date created)

Paul Newcome

@jennbark Try something like this...

=COUNTIFS(Created:Created, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

Eric Isaacs

Hi Team -

I have been trying to get this formula to work but I'm obviously missing something, can anyone see where I'm going wrong here?

=COUNTIFS([Logic Based 10]:[Logic Based 10],NOT(ISBLANK(@cell)),[Logic Based 11]:[Logic Based 11], WEEKNUMBER(@cell)=WEEKNUMBER(TODAY()))

Paul Newcome

@Eric Isaacs Are you getting an error message or an unexpected count?

Eric Isaacs

https://community.smartsheet.com/discussion/comment/392357#Comment_392357

Hi @Paul Newcome I am getting the #UNPARSEABLE

Paul Newcome

@Eric Isaacs In that case, double check your column names match what you have in your sheet.

Eric Isaacs

https://community.smartsheet.com/discussion/comment/392376#Comment_392376

@Paul Newcome do you mean where I have changed the reference to "[Logic Based ##]"?

=COUNTIFS([Logic Based 10]:[Logic Based 10],NOT(ISBLANK(@cell)),[Logic Based 11]:[Logic Based 11], WEEKNUMBER(@cell)=WEEKNUMBER(TODAY()))