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

Options

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)

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Pam Grant
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.