Count conversations every week

Laura G

Hi All

I am looking to count total conversations/comments our team is having per week.

I have set up a count comment sheet which will receive rows from the sheet 1 when anyone adds a comment to any row.

The count comment sheet includes a "latest comment date added" column. This date auto populates when a row is added to the sheet.

For dashboard board purposes; i have a master metrics sheet. I want to count how many "latest comment date added" row is added in a 1 week date range. I have used this formula which doesn't show an error but the count is wrong

=COUNTIFS({Count Comment Range 1}, ">14/01/24", {Count Comment Range 1}, "<=21/01/24")

This is the sheet i am referencing but my formula counts 0 when it should be 2.

Any insight into what i am missing?

Thanks so much for your help

jmyzk_cloudsmart_jp

Hi @Laura G

Thank you for the interesting question. First, if the "latest comment date added" column is a Date column, you must use the DATE() function.

example: >DATE(2024,1,14) and <=DATE(2024,1,21)

I use the Sheet Summary fields, but the formula structure is the same.

=COUNTIF([latest comment date added]:[latest comment date added], AND(@cell > DATE(2024, 1, 14), @cell <= DATE(2024, 1, 21)))

Since changing the DATE()'s argument every week is cumbersome, you can automate this by using the WEEKNUMBER and TODAY functions.

Luckily, "Smartsheet calculates week numbers based on Mondays. "

The formula to determine if This Week is as follows;

=IF(WEEKNUMBER([latest comment date added]@row) = WEEKNUMBER(TODAY()), 1, 0)

Then, you can use this to count this week's comments, as shown in the image below.

(The "This Week w/o Sun & Sat" column copes with the situation where you only want to count weekday events.)

https://app.smartsheet.com/b/publish?EQBCT=9c11158dbda444368460f66d917fe237