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: * 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) * 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"

Count the number of instances per week?

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:

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)
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"

Find more posts tagged with

Accepted answers

All comments

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")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.