SUMIFS not Working

Trying to SUM total from sheet 2 (column titled Grand total...)when the Job No column from Sheet 2 shown below matches the Job No column from Sheet 1(shown below)



Here's formula - but keeps returning 0?

=SUMIFS({Job Cost Original Budget Close-out Data Range 1}, {Job Cost Original Budget Close-out Data Range 2}, =[Job No]@row)

Thank you!!

Best Answer


  • MCorbin
    MCorbin Overachievers Alumni

    The formula should work, but without being able to see what your Ranges are referencing, it's a little difficult to troubleshoot.

    Here are some things I would check:

    1) Make sure the Job No in both sheets is the same column/data type. I'm noticing one is left justified and one appears to be centered. If Smartsheet it seeing 1 as text and the other as a number, it would not match.

    2) Double Check your Ranges and make sure they're referring to the columns you think they're referring to. This is what I see most commonly - someone is creating multiple formulas and accidentally re-defines what one of the ranges is looking at.

    3) You don't need the = in front of [Job No]@row. (It shouldn't make a difference, but something to try, just in case...)

  • Debbie Petersen
    Debbie Petersen ✭✭✭✭✭
    Answer ✓

    Thanks - I had done 1 and 2 already. Pulled the = but still doesn't work. Thank you so much for the reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!