SUMIF - Referencing another sheet

edited 12/09/19 in Formulas and Functions
02/06/19 Edited 12/09/19

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!



  • 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 NewcomePaul Newcome ✭✭✭✭✭

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


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

  • 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?

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

Sign In or Register to comment.