SUMIFS Question for adding a criteria for a column sum function

lovelyemy
lovelyemy ✭✭
edited 04/10/23 in Formulas and Functions

Hello! I am working on creating a travel budget dashboard for my team. I threw in some random numbers but essentially I am creating this data sheet to reference my main sheet where I house all travel budget requests.

Capture.PNG

In my other sheet I treat as a data repository of all requests I receive. The above {cost} and {Dom or Int} is referencing a different sheet.

Capture.PNG

In that "international cost" above I am trying to get the cost sum of anything that is marked as domestic in my other sheet. Am I messing up the formula?


Any feedback would be greatly appreciated!

Tags:
· Share on FacebookShare on Twitter

Answers

  • Austin Smith
    Austin Smith ✭✭✭✭✭

    @lovelyemy

    Make sure the reference word is in quotes (only error I see, if it's even an error given sample status). You can use, but don't need, a sumifs with only one qualifier (formula is generally correct though). Try formula below.

    =SUMIF([Domestic or international]:[Domestic or international], "Domestic", Cost:Cost)

    All references can come from original data sheet.

    What error are you getting from the formula?

    · Share on FacebookShare on Twitter
  • lovelyemy
    lovelyemy ✭✭
    edited 04/10/23
    https://community.smartsheet.com/discussion/comment/370994#Comment_370994

    Hi there, I am actually referencing a separate sheet that's why I called it {Dom or Int} for the Domestic or International Column using the reference another sheet link. I just gave this a shot and am getting the #UNPARSEABLE error message.

    Capture.PNG


    · Share on FacebookShare on Twitter
  • Austin Smith
    Austin Smith ✭✭✭✭✭

    @lovelyemy

    Typically #unparseable has to do with your reference data having something in it that doesn't compute. Can you run a sum formula for costing inside of the origin sheet without errors?

    · Share on FacebookShare on Twitter
  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    Hey,

    I'm too lazy to figure out all the details, but it seems that your syntax is wrong. When you cross reference a column in another sheet you dont use {name}:{name}, the reference is just {name}.

    Also, when you want to use SUMIF and = a word, you need to put that word in quotations. "word".

    I'm writing this whilst referencing your first screenshot.

    If you make these adjustments your SUMIF formula will work.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!