Could you please provide an example of a formula to count for the last week?

Angie Hatfield

Could someone please provide an example of a count during the past week?

Each row has a date listed, and several rows could have the same date. How do I enter a count of all of those dates during the last week? What if I need to add if it's only a specific status? Where do I set the formula within the sheet? An empty cell to always show the count? Will it automatically change next week for the following week? Could you please provide a screenshot example?

I can usually figure formula's out, but this one confuses me somewhat, even with those I found in the Community.

This count will be shown on a Sight.

Thanks so much, I greatly appreciate it.

 

Angie

Angie Hatfield

I want to clarify that I do mean last week, not the last seven days.

 

Thanks very much

J. Craig Williams

Angie,

(UPDATED)

What is your start of week?

USA typically starts on Sunday, though ISO 8601 starts on Monday.

I'll assume you start on Sunday.

See next comment post for starting on Monday.

Further, I will assume by 'last week', you mean any date that was Sunday-Saturday of the previous week. 

If today is Monday Oct 2 2017, then any date from Sep 24 to Sep 30 2017 will be counted. 

Likewise if today if Saturday Oct 7, the same dates are counted.

Start with COUNTIFS, that will allow you to expand it later for more criteria. COUNTIF will work for a single criterion, COUNTIFS works for both single criterion and multiple criteria.

As to where to put it: any column other than the 'columns of interest'. That will allow you to utilize the column range feature (if that works for your sheet layout).

The formula I would use would be this:

=COUNTIFS(Finish:Finish, AND(@cell >= (TODAY() - WEEKDAY(TODAY()) - 6), @cell < (TODAY() - WEEKDAY(TODAY()) + 1)))

where I am looking at the [Finish] column in its entirety.

The AND() function surrounds two criteria for determining the range of dates.

One could also use

=COUNTIFS(Finish:Finish, criterion 1 from AND, Finish:Finish, criterion 2 from AND) but I think the AND actually helps to determine what the formula is trying to do.

I hope this helps.

Craig

J. Craig Williams

For Monday, I revert to using WEEKNUMBER.

https://help.smartsheet.com/function/weeknumber

When I first tested, I ran into @cell throwing errors when it runs into a blank. I consider this a bug, especially given the prevalence of needs for COUNTIF, SUMIF, COLLECT,and other functions need to parse a range. Smartsheet Dev's likely disagree with me.

Also, there is some discrepancy to the WEEKNUMBER function and WEEKDAY function as to when the week starts which may cause slight complications later or if you have to deal with other teams that count things like weeks differently.

That said, for Smartsheet WEEKNUMBER's start on Monday, so we can use it to determine what was last week like this:

=COUNTIFS(Finish:Finish, IFERROR(WEEKNUMBER(@cell), 0) = (WEEKNUMBER(TODAY()) - 1))

Note the IFERROR wrap to catch the blank dates.

I hope this helps too.

Craig

Angie Hatfield

Thank you Craig, you have been very helpful!