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

Drew Wort

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

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!

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. 👍️