When Counting by week how do I only include the current year?

My formula for counting by week is:

=COUNTIFS({BBS Data Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), {Staff}, ="John Smith")

This works pretty well but it is counting events that occured in previous years. I only want to include THIS YEAR in the count.

Thanks in Advance

Find more posts tagged with

Conditional Formatting

Accepted answers

Drew Wort

=COUNTIFS({BBS Data Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), {BBS Data Range 1}, YEAR(@cell) = YEAR(TODAY()), {Staff}, =Staff@Row)

This ended up working, couldnt have arrived at this without your help.

Thanks!

All comments

kowal

hi @Drew Wort,

if you want to have dynamic year you need to create two helping columns one will be date and you keep there =today(); and second will be =year(todays)…. in this case the second column will take the year from todays column always.

hope this helps.

Paul Newcome

Your date criteria would change to

=AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())

Drew Wort

=COUNTIFS({BBS Data Range 1}, =AND(IFERROR(WEEKNUMBER(@cell ), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()), {Staff}, ="John Smith"))

I tried this to no avail. Am I missing something? I keep getting invalid data type/

@Paul Newcome

Paul Newcome

Sorry. No equals before the AND function.

Drew Wort

=COUNTIFS({BBS Data Range 1}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), {BBS Data Range 1}, YEAR(@cell) = YEAR(TODAY()), {Staff}, =Staff@Row)

This ended up working, couldnt have arrived at this without your help.

Thanks!

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.