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.

    SUMIF - Referencing another sheet

    ✭✭
    edited 12/09/19 in Formulas and Functions

    I'm trying to sum based on certain Streams(column name) in another sheet 'Register' 

    Stream data is as:

    Emergency

    Youth

     

    The formula I've used is:

    =SUMIF({Register Range 3}:{Register Range 3}:, "Emergency")

    I'm getting an unparseable error

     

    Please help!

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Comments

    • ✭✭✭

      You need to create two referenced ranges on the Register sheet, one containing the column "Stream", and another containing the column (let's call it "Amount") that you want to total. The formula becomes:

       

      =SUMIF({Register Amount Column},{Register Stream Column},"Emergency")

      (assuming you rename the ranges as appropriate, rather than just accepting the defaults).

      There is no need for ":", the external range must be defined as all the cells you want to look at, either the whole column or a fixed subset.

      I hope that makes sense...

       

      · Share on FacebookShare on Twitter
    • Community Champion

      I think you're looking for a count instead of a sum. Try this...

       

      =COUNTIF({Register Range 3}:{Register Range 3}:, "Emergency")

      CERTIFIED SMARTSHEET PLATINUM PARTNER

      10xViz

      Would you benefit from getting my help in person?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi - I'm trying to reference another sheet to total prices in one column that have a specific word in another column. However, I am getting the total for the entire column when I use:

      =SUM({Social Media Data 2021 Range 1}, "Orthopedics", {Social Media Data 2021 Range 2}) - I get the total for all, not just "Orthopedics"

      Based on the info above I tried to use:

      =SUMIF({Social Media Data 2021 Range 1}, {Social Media Data 2021 Range 2}, "Orthopedics") - I get #INCORRECT ARGUMENT

      =COUNTIF({Social Media Data 2021 Range 1}: {Social Media Data 2021 Range 2}:, "Orthopedics") - I get #UNPARSEABLE

      Can someone please help me figure out how to fix my query?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      I think I figured it out. What you can't see is what columns I chose. The correct answer is:

      =SUMIF({Social Media Data 2021 Range 2}, "Orthopedics", {Social Media Data 2021 Range 1})

      In other words, SUMIF (select the column with the word you want tallied), "Word", (select the column with the amounts you want totaled based on the word)

      I hope this helps others in search for the answer.

      · 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