What is the best way to get a running count of entries made per day?

Sheila Ruby

I am using a feedback log smartsheet and I also do a report out each day on how many feedback entries were entered the day before. I would like to have this information populate automatically in another smartsheet that I can create a line graph off of to show the trends of how many feedback entries per day. Can anyone tell me what formula to use or the best method to do this? I need the formula to reference the dates from my feedback log. I sent an attachment of what I am trying to get for my chart using smartsheet.

Capture.PNG

Josh Strong

I would use the CountIF function, set up a sheet and in 1 column put all the dates (Date), then in the second column the formula would look like

=Countif{reference date comlumn in feedback sheet}, [Date])

Regards,

Josh

Sheila Ruby

How do I make this a continuous formula? I know how to get it to count dates for dates that are in the past, but how can I get it to automatically reference my sheet tomorrow and count how many entries were entered today and plug it into a cell?

Example: tomorrow is 2/18/20 and I report out how many entries were made on 2/17/20. I do this every day, and I want to avoid having to manually count the entries and enter them on another sheet. I can give you more details if needed. I can always send pictures of the sheet I am working off of.

Thanks in advance!

MCorbin

I usually create a metrics sheet for this, using a rolling time period (7 days, 6 months, etc.)

I'd do columns for Today, Yesterday, 2 days ago, 3 days ago, etc...

First row would have my test dates: Today(), Today(-1), Today(-2), Today(-3), etc...

Next Row would then be my data counts:

=Countif({date column in data sheet}, [Test Date]$1)

Where Date Column in Data sheet is the data column in your source data

Test date is the calculated field you created ie: Today(-1)

The $1 would correspond to the row where your test date is located

By doing it this way, you can copy the formula to each column and it will automatically pull the test date for that column.