Is there a better way to write a formula that collects data for each week of the year?

Question

Hello

This is my current formula.  It works but is laborious to write out.  Is there a better way?

=COUNTIFS({CBTC Feeder File Range 2}, >=DATE(2020, 6, 1), {CBTC Feeder File Range 2}, <=DATE(2020, 6, 7), {CBTC Feeder File Range 1}, FIND("ITAV", @cell) > 0)

Mark Cronk · Accepted Answer

Hi @Pam Grant ,

I'd recommend using WEEKNUMBER( ) instead of the date range. This funcrion returns the week of the year, from 1-52, for a date.

Your formula would be:

=COUNTIFS({CBTC Feeder File Range 2}, WEEKNUMBER(@cell) = 1, {CBTC Feeder File Range 2}, {CBTC Feeder File Range 1}, FIND("ITAV", @cell) > 0)

The "1" is the 1st week in the year. You'll need to copy and paste it 51 times, once for each week, and change the number. Otherwise you can replace the 1 with a fixed reference to a cell, say the first row in each column. Enter 1-52 in those cells and lock the row.

Not sure I'm clear enough, but let me know if you need more instruction.

Good luck.

Mark

Mark Cronk · Accepted Answer

Merry Christmas @Pam Grant ,

Good thought on the year. Your formula will work but you could use an AND function to avoid having the computer search the same range twice:

=COUNTIFS({CBTC Feeder File Range 2}, AND(YEAR(@cell) = 2020, WEEKNUMBER(@cell) = 1), {CBTC Feeder File Range 2}, {CBTC Feeder File Range 1}, FIND("ITAV", @cell) > 0)

Thank you for allowing me to help you and for using the Community.

Be Well,

Mark