Sum by Date Formula - Need Help

Options

=SUMIF({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021)

I am trying to use this formula to sum the total grams if the harvest date was in the year 2021 but am getting "Invalid Operation".

=SUMIFS({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021, {CFL Batch Summaries 2021-Present Grade}, =1)

I am also trying the above formula to try and sum for the year and grade but am getting "Invalid Data Type".

Tags:

Answers

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    Hi @Nate420,

    in this formula I see a few issues: =SUMIF({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021)

    I'm gonna assume the following:

    {CFL Batch Summaries 2021-Present Flower g} = weight to sum in sheet

    {CFL Batch Summaries 2021-Present Harvest Date} = year column in sheet

    YEAR(@cell) = 2021 = criterion

    If this is correct, the formula should read =

    =sumif({CFL Batch Summaries 2021-Present Harvest Date}, 2021, {CFL Batch Summaries 2021-Present Flower g})

    For the second formula: =SUMIFS({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021, {CFL Batch Summaries 2021-Present Grade}, =1)

    I think you want this:

    =SUMIFS({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, 2021, {CFL Batch Summaries 2021-Present Grade}, 1)

    Hope this helps

    best,

    Brad

    www.MVPOPS.com

  • Nate420
    Nate420 ✭✭
    Options

    This formula brings back 0.0 for some reason but there should be a large number. Maybe because I have a full date in the column and not just the year?

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    @Nate420 that means the formula is working. Yes, if you don't have a year column than it won't return the proper number. Add a column called year, add the year formula =Year(date field@row), make it a column formula. Use this year column as your criteria range column.

    best,

    Brad

    www.MVPOPS.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!