Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    SUMIFS not Working

    ✭✭✭✭✭
    in Formulas and Functions

    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)

    SHEET 2

    image.png


    SHEET 1

    image.png


    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!!

    · Share on FacebookShare on Twitter

    Best Answer

    Answers

    • 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...)

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      Answer ✓

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions