COUNTIF & WEEK

NickJonesLG

Hi All,

A client wants to be able to see how many calls were logged in thier current working week . However the working week begins on a Friday 00:00am and ends Thursday 11:59pm.

I'm after a formula to count all entries with the word "Aberdeen" in the Column called "Club" in the mentioned working week . This counter would then be reset when the new working week begins

I have the Count formula : =COUNTIFS(Club:Club, CONTAINS("Aberdeen", @cell))

I just need to include the Working Week range to the formaula .

Thankyou

Eric_

You need to modify the WEEKNUMBER() value based on the WEEKDAY() value.

At the time of writing this email, we are Thu 15-Feb, real week 7. What will tomorrow (Friday) be in your mind, week 7 or week 8?

If tomorrow is week 7:

In Smartsheet, the week starts on Monday (Day 2), at least for me in Europe. For you it starts on Friday (Day 6). So in your logic, Day 6, 7 and 1 are "real week" and the next Day 2-5 are "real week"-1

New week number =IF(OR(WEEKDAY(Date@row) > 5, WEEKDAY(Date@row) = 1), WEEKNUMBER(Date@row), WEEKNUMBER(Date@row) - 1)

If tomorrow, we are already Week 8, then

New week number =IF(OR(WEEKDAY(Date@row) > 5, WEEKDAY(Date@row) = 1), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))

You can add this value to a new "Week" column and then count how many occurences of the call's week matches the same week number for the given Club.

Something like:

=COUNTIFS(Club:Club, Club@row, Week:Week, Week@row)