Count the number of entries in the last 7 days

Cristian

Hello

I have a few questions

1. I want to know if there's a way to count the number of entries made for the last 7 days. For example, I have a sheet with hundreds of entries which collects customer requests. I would a way to quickly display the number of entries for the last 7 days.
2. Count the number of entries made weekly, M-F. For the same sheet with customer request. I would like a way to display the number of entries made over a standard M-F week. This should be for the most current week.
3. Count the fluctuation from previous week. Example, based on information from the previous week entries and comparing it to current week entries, provide percentage of either increase or decrease. If increase highlight in green. If decrease, highlight in red.

I have a few pictures I gathered from the web.

Any help would be appreciated.

Thanks

Cristian

Is there any other information some one can provide for the last question?

Thanks

Jason Tarpinian

If you create a helper sheet build out the helper columns I have shown below. Start off by creating a Row # column. This is done with using the system column auto number as a helper.

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Then fill out your count range dates. Below I did M-F (which knowingly would then miss everything on the weekends, so adjust as needed).

The "Inputs / Week" would then be a cross-sheet formula that counts up the values on your submission sheet based on the system "Created date" column. So where I have "[Date Created]:[Date Created]", you'd have something like {Date Created} for the cross sheet reference.

=COUNTIFS([Date Created]:[Date Created], >=[Count Range Start]@row, [Date Created]:[Date Created], <=[Count Range End]@row)

The Current/Previous week formulas can then be used to tell you which week is current so you can pull in the proper "Inputs / Week" to do all the fun maths and get the changes over weeks

"Current Week" =IF(AND(TODAY() >= [Count Range Start]@row, TODAY() <= [Count Range End]@row), true)

"Previous Week" =IF(INDEX([Row #]:[Row #], MATCH(true, [Current Week?]:[Current Week?], 0)) - 1 = [Row #]@row, true)

Cristian

Thanks for the info Jason.

A few questions. How did you get the inputs/weeks ct? Did you use a formula to get that from another sheet?

Can I add the Row ID, Row#, ct range start, and ct range end in my originating sheet instead of creating a helper sheet?

Cristian

Jason

I was able to figure out my previous questions. But I have another one. What is the significance of the Row ID and Row #? Now sure they're suppose to do. Also, how can I get the most latest current count for the last week to appear in one cell. Want to add this cell to a dashboard not the entire sheet if possible. Thanks.

Jason Tarpinian

The Row ID + Row # is just a guarantee for the current week/previous week formulas. If you start deleting rows, or rearranging rows, you can't use Row ID to determine the previous line. Row # is 100% consistent, so in the case that this week is row 5, you will always know that the previous week will be in row 4.

For the current week, use either of these formulas

This will tell you the entries for the current week =INDEX([Inputs / Week]:[Inputs / Week], MATCH(true, [Current Week?]:[Current Week?], 0))

Per your original request, you mentioned last 7 days, that would be =COUNTIFS([Date Created]:[Date Created], @cell > TODAY(-7))

And to your other question about using the original sheet vs a helper sheet, I'd recommend the helper. You CAN use the same, but for the same reason as why I use Row #, if you start deleting rows/moving rows, sorting the sheet, it can really mess things up.

Cristian

I was able to get it to work. Thank you.

I was also able to get count for the last 7 days shown here in the green circle.

Is there a way to show the same for the last 7 days for the deviation difference? The formula I used to get the deviation difference is in red circle. Need that one in once cell as well.

This way I can populate my dashboard with two cell values, one with the ct for the last 7 days and the other with the deviation difference.

Thanks again. :)

Cristian

Is there any other information some one can provide for the last question?

Thanks