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

Pam Grant

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

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

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

Mark Cronk

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

Pam Grant

Thank you, @Mark Cronk!

This is very helpful!

I will need to do this for every year as we collect the data, so I'm thinking the formula will look something like this?

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

Mark Cronk

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