# 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?

«1

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

• ✭✭✭✭✭

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

• ✭✭✭✭✭
edited 08/13/20

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:

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

• ✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!