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.

    Cross Referencing and sum formula

    ✭✭✭✭
    in Formulas and Functions

    Hi all,

    I am working on this new sheet, and have successful formulas from previous sheets that work great. However, I have hit a roadblock here on this one.

    This formula looks at the Errors sheet I have, and a column that produces a 1 if there is data in the column next to it. With that, I have a consolidation sheet that counts the points based on a certain criteria. It is only producing a zero instead of summing.


    =IF(ISBLANK(Agent@row), "", SUMIFS({Errors Range 10}, {Errors Range 10}, @cell = Agent@row))


    The Agent@row is polling the specific information from the Errors form and consolidated the Agent to one indexed cell. The point of this is to look for all the instances of that agent on the other form and sum up the values of the noted errors.


    I hope this makes sense LOL. Thanks for any assistance on this.

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    Answers

    • Community Champion

      Make sure you are converting the counted SUM of errors to a value using =VALUE(formula to count errors here). I have noticed that this helps when trying to SUM.

      I suggest not using the ISBLANK function, as it is quicker to just consolidate with <> which is the same thing but without the function.

      =IF(Agent@row <> "", "", SUMIFS({Errors Range 10}, {Errors Range 10}, @cell = Agent@row))
      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Stey, thanks for the tip on the <> function.


      Now to the meat and potatoes. The formula produced nothing. I ended up cleaning up the references and moved it to Range 2.

      image.png

      This is what Range 2 is on the Errors sheet

      image.png


      · Share on FacebookShare on Twitter
    • Community Champion
      edited 11/11/23 Answer ✓

      Oh, your issue is that you need to change the errors to match with the agent, and also change the logic of the IF.

      So now we have:

      =IF(agent@row = "", "", SUMIFS({Errors Range}, {Agent}, agent@row))

      So, if agent is blank, blank returns. If agent is not blank, SUMIFS function occurs to tally the errors range based on the {agent reference} matching with agent@row.

      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      came back as invalid reference. Here is the original formula I used on another sheet that works flawlessly. It is the same concept.

      =IF(ISBLANK(Agent@row), "", SUMIFS({Battle Pass Submission Form Range 3}, {Battle Pass Submission Form Range 1}, @cell = Agent@row))

      · Share on FacebookShare on Twitter
    • Community Champion
      Frank Hammond

      Invalid reference just means you need to update the reference, then it should be working fine

      Sincerely,

      Jacob Stey

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      I am an idiot. LOL, I never did anything with the {Agent} bit. Thanks for your assistance on this one, Jacob.


      =IF(Agent@row = "", "", SUMIFS({Errors Range 2}, {Agent}, Agent@row))

      · Share on FacebookShare on Twitter
    • Community Champion
      Frank Hammond

      Happy to help 👍

      Sincerely,

      Jacob Stey

      · 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