SUMIF Doesn't Work When I Reference Another Sheet

When I use this formula it works

=SUMIF([Target Month]:[Target Month], "February", [# of Devices]:[# of Devices])

But when I put it in another sheet and reference it I get an unparseable error. Any ideas why?

=SUMIF({Branch Refresh Schedule}[Target Month]:[Target Month], "February", [# of Devices]:[# of Devices])

Best Answer

  • Summer
    Summer ✭✭✭
    edited 02/25/22 Answer ✓

    @tlclark I've mocked it up on my end and this formula works for me:

    =SUMIF({Branch Refresh Schedule| Target Month}, Month@row, {Branch Refresh Schedule | # of Devices})

    Range: {Branch Refresh Schedule| Target Month} the group of cells to evaluate

    Criterion: Month@row the condition that defines which numbers to add. In this case it refers to the month column on the Formula sheet and takes the place of "February". Can easily be replaced back with "February"

    Sum Range: {Branch Refresh Schedule | # of Devices} the group of cells to add together. Could be replaced with the [# of Devices]:[# of Devices] range if it's on the same sheet as your formula.


    Formula Sheet (Formula is in the # of Devices Column)

    Branch Refresh Schedule


Answers

  • Summer
    Summer ✭✭✭
    edited 02/25/22

    Hi @tlclark

    My original answer was incorrect.

    For the first part of the formula where you are selecting the range, utilize the "Reference Another Sheet" option and select the range on the source sheet that includes the Target Month.

    So your updated formula would read something like this

    =SUMIF({Branch Refresh Schedule Range 1}, "February", [# of Devices]:[# of Devices])

  • tlclark
    tlclark ✭✭

    Hi @Summer

    I did try that as well and it didn't work. Not sure what I missed.

  • Summer
    Summer ✭✭✭
    edited 02/25/22 Answer ✓

    @tlclark I've mocked it up on my end and this formula works for me:

    =SUMIF({Branch Refresh Schedule| Target Month}, Month@row, {Branch Refresh Schedule | # of Devices})

    Range: {Branch Refresh Schedule| Target Month} the group of cells to evaluate

    Criterion: Month@row the condition that defines which numbers to add. In this case it refers to the month column on the Formula sheet and takes the place of "February". Can easily be replaced back with "February"

    Sum Range: {Branch Refresh Schedule | # of Devices} the group of cells to add together. Could be replaced with the [# of Devices]:[# of Devices] range if it's on the same sheet as your formula.


    Formula Sheet (Formula is in the # of Devices Column)

    Branch Refresh Schedule


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!