Formula to show number of requests entered each week
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?
Answers
-
I also forgot to mention, I'm hoping to capture this information on a Dashboard as well.
-
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.
-
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
-
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.
-
I'm not sure I follow.
Let me know if I'm getting this correctly:
- Create a new column in my sheet
- Use the same formula provided above
- 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
-
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)
-
That worked! Thank you so much!
-
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())
-
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)
-
@jennbark Try something like this...
=COUNTIFS(Created:Created, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))
-
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()))
-
@Eric Isaacs Are you getting an error message or an unexpected count?
-
Hi @Paul Newcome I am getting the #UNPARSEABLE
-
@Eric Isaacs In that case, double check your column names match what you have in your sheet.
-
@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()))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!