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:




The formula I've used is:

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

I'm getting an unparseable error


Please help!



  • Andrew Stewart

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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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


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

  • Cindi Meche
    Cindi Meche ✭✭✭

    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?

  • Cindi Meche
    Cindi Meche ✭✭✭

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!