How do I extract data from a column that represents a week or month?

Options

Lets say I count apples sold and I input the data in column [Number of apples sold] which is next to a date column. Now I want to find out how many apples I sold in a given week. I use this formula:

=SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = 44, Date:Date, YEAR(@cell) = 2021)


This works but I have to change the WEEKNUMBER for every week which is a lot of work and I will also have to update the YEAR figure every year.

I use the same method for extracting "apples sold" on a monthly basis with the same problem.

I think the solution is to compare the WEEKNUMBER of the Date column with a helper column that has the WEEKNUMBER repopulated. I just can't get that to work.

Thanks for the help!


Wolfram

Tags:

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Wolfram

    You'll need to have the week or month listed somewhere, either directly in the formula or in a cell you can reference.

    For example, if you have a column with all the week numbers listed down each row, then you can use the cell from that column as the week number to look for:

    =SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = [Week Number]@row, Date:Date, YEAR(@cell) = Year@row)

    Is that what you were looking to do?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Wolfram
    Wolfram ✭✭
    Answer ✓
    Options

    @Genevieve P.


    Thats perfect. Thanks.

Answers

  • Genevieve P.
    Options

    Hi @Wolfram

    You can use the TODAY function to help you with this!

    Are you looking to calculate last week's totals? If so, we can find the WEEKNUMBER(TODAY()) - 1, which will tell us last week's number.

    Then we can do the same thing with the YEAR function... YEAR(@cell) = YEAR(TODAY())

    Try this:

    =SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))


    For your month formula, I would presume it's the same sort of thing. If you want to find last month, you could do this:

    =SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))

    However this gets a bit complicated if you're in January. I would write two formulas and have an IF statement identify what month you're in, like so:

    =IF(MONTH(TODAY()) = 1, SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = 12, Date:Date, YEAR(@cell) = YEAR(TODAY()) - 1), SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY())))


    Let me know if this is the right solution for you or not.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Wolfram
    Wolfram ✭✭
    Options

    Hi @Genevieve P.


    Thanks!


    I don't want to have the data for last week or last month. I want to have the data for a given week or month. E.g. week 22 or month 8.

    What I want to avoid is having to input the week number 52 times.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Wolfram

    You'll need to have the week or month listed somewhere, either directly in the formula or in a cell you can reference.

    For example, if you have a column with all the week numbers listed down each row, then you can use the cell from that column as the week number to look for:

    =SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = [Week Number]@row, Date:Date, YEAR(@cell) = Year@row)

    Is that what you were looking to do?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Wolfram
    Wolfram ✭✭
    Answer ✓
    Options

    @Genevieve P.


    Thats perfect. Thanks.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!