I need help calculating the sum of one column based on a date in another column

OK, so I'm trying to calculate values (in one column) based on the Month (in another column). This is what I have tried:

=SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, MONTH(@cell) = MONTH(TODAY(), {Sheet - Sales Pipeline Range 3}))

However, it returns 0. I'm referencing another sheet if that makes a difference.

Not sure what I'm doing wrong, hope you can help!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @killis_

    The formula is structured correctly! And yes, your column names are spelled the same as your first screen capture. However it looks like you're writing the formula in a second sheet, separate from where the columns are located. Is that correct?

    If so, you'll need to use {cross sheet references} instead of [in-sheet].

    Here's documentation on references in formulas: Create a Cell or Column Reference in a Formula and Formulas: Reference data from other sheets

    In this case, you'll want a structure like so:

    =SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})

    Cheers,

    Genevieve

Answers

  • killis_
    killis_ ✭✭

    I've expanded on this a little:

    =SUMIF([{Expected Close Date}]:[{Expected Close Date}], MONTH(@cell) = 11, [{Forecast Amount}]:[{Forecast Amount}])

    Getting UNPARSABLE. Know I'm doing something wrong, I just can't work it out.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @killis_

    For your first formula, it looks like you're missing a closing parentheses after the TODAY() function, to close out your MONTH( ___):

    =SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, MONTH(@cell) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})

    For your second formula, can you clarify if you're putting this formula in the same sheet as your columns or are you looking into a separate sheet? You're combining ways of referencing formulas. If you're looking in the same sheet, there's no need for {curly brackets}. See: Create a Cell or Column Reference in a Formula

    Try:

    =SUMIF([Expected Close Date]:[Expected Close Date], MONTH(@cell) = 11, [Forecast Amount]:[Forecast Amount])


    I will also note that the MONTH function can error when it hits a cell with a blank value or text value. To get rid of this, I would suggest wrapping it in an IFERROR, like so:

    =SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})

    OR

    =SUMIF([Expected Close Date]:[Expected Close Date], IFERROR(MONTH(@cell), 0) = 11, [Forecast Amount]:[Forecast Amount])


    Cheers,

    Genevieve

  • killis_
    killis_ ✭✭
    edited 11/17/22

    Hi @Genevieve P.

    Thank you for coming back to me!

    I have tried each option, and I am still getting UNPARSEABLE.

    I can confirm I am using the same sheet to reference from.

    🤔

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @killis_

    Thanks for this screen capture, this helps! I can see that the column names are not highlighting in colours - this to me indicates there could be a slight difference in how the column is spelled versus what's in your sheet, is that possible?

    Can you paste a screen capture of only your column names?

  • killis_
    killis_ ✭✭

    Thanks @Genevieve P. , please see the column names:

    I see there is a difference in the column name so have updated it, but I still get UNPARSEABLE:

    I know I'm so very close, could it be to do with the sheet referencing?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @killis_

    The formula is structured correctly! And yes, your column names are spelled the same as your first screen capture. However it looks like you're writing the formula in a second sheet, separate from where the columns are located. Is that correct?

    If so, you'll need to use {cross sheet references} instead of [in-sheet].

    Here's documentation on references in formulas: Create a Cell or Column Reference in a Formula and Formulas: Reference data from other sheets

    In this case, you'll want a structure like so:

    =SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), {Sheet - Sales Pipeline Range 3})

    Cheers,

    Genevieve

  • killis_
    killis_ ✭✭

    @Genevieve P. You are wonderful!

    Thank you so much it worked, I'm so grateful, been wrestling with that for days!

    Have a great day, Holly 😘

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!