Count the number of instances per week?

Sarah Cobb

Hi-

I have a formula that counts the number of times "User Received"is in the Status column for the month of February. =COUNTIFS(Status:Status, "USER RECEIVED", [Need By Date]:[Need By Date], IFERROR(MONTH(@cell), 0) = 2)

two questions:

1. how can I make this formula count the Feb instances as separate instances if for separate years? How will the data from 2021 be differentiated from 2022? (I could do a filter, but I'd rather have it in the formula)
2. How can I count a range of dates of an instance? For example, how could I count an instance that happens multiple times during week 1/week 2/etc of February?

Here's another formula I tried:

=COUNTIFS([Need By Date]:[Need By Date], >(DATE(2021, 2, 1) + 6), Status:Status, ("User Received")) but it returns "0"

Mark Cronk

Hi @Sarah Cobb ,

For the year use:

=COUNTIFS(Status:Status, "USER RECEIVED", [Need By Date]:[Need By Date], AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021))

For the week try:

=COUNTIFS([Need By Date]:[Need By Date], WEEKNUMBER(@cell)=WEEKNUMBER(2/1/2021), Status:Status, "User Received")

Work?

Mark

Sarah Cobb

=COUNTIFS([Need By Date]:[Need By Date], WEEKNUMBER(@cell)=WEEKNUMBER(2/1/2021), Status:Status, "User Received")

Hi Mark,

I reached out to smartsheets directly and they sent that same formula, so we know you're right in theory!

It's not working though. I was trying to plug it in last night too and keep getting #Unparsable 😱

Paul Newcome

You need a DATE function.

=COUNTIFS([Need By Date]:[Need By Date], WEEKNUMBER(@cell)=WEEKNUMBER(DATE(2021, 02, 01)), Status:Status, "User Received")