SUMIFS Cross Sheet Issue

ryan.diamond55371
ryan.diamond55371 ✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello Everyone!

I am needing help with the SUMIFS formula for cross sheet reference but when I enter multiple criteria, it returns a value of 0. What I am trying to accomplish is a sum for Sheet-2 based on multiple criteria from Sheet-1:

Criteria from Sheet-1

  • Job ID
    • 216116
  • Cost Code ID
    • 7
    • 15
    • 16

Sum Range: Act. Expenses

Basically I want to add all the specified cost codes from a specific job ID and when I create the formula the return value on Sheet-2 for the Row: Engineering, Column: Act. Expenses is 0.

Let me know what you all think, I was wondering if blank cells would affect the process but I am not sure.

-RD

Sheet-1.JPG

Sheet-2.JPG

Comments

  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭

    You can void blanks by using ISBLANK https://help.smartsheet.com/function/isblank

    Basically you need to use a cell reference since this apepars to b e two separate sheets.

    1. Go to the sheet you want the sumifs in
    2. type in =SUMIFS(
    3. Now you are going to see "Add a Reference" as a dropdown while typing. Click it. Navigate to your other sheet and click the column name and name the reference something like "Act Expenses"
    4. Save
    5. Now you can use {Act Expenses} anywhere in that sheet to reference that entire column! Your code should look like =SUMIFS({Act Expenses}
    6. Add a comma so you're at =SUMIFS({Act Expenses},
    7. Now it's time to do the job ID, you're going to create a reference this time for {Job ID}
    8. Your code should look like =SUMIFS({Act Expenses}, {Job ID}
    9. Add a comma =SUMIFS({Act Expenses}, {Job ID}, 
    10. Now we're going to add a criteron which could be as simple as [Job ID]1
    11. Your code should be like =SUMIFS({Act Expenses}, {Job ID}, [Job ID]1 -- This now will reference the cell Job ID 1 and compare it against Job ID. The Act Expenses is the sum. 
    12. You said you want to use the COST ID, how are you going to put that in? Your second sheet has no references to it. Basically repeat what we did for Job ID for the Cost Code ID. So in the end yourt formula will look similar to this: =SUMIFS({Act Expenses}, {Job ID}, [Job ID]1, {Cost Code ID}, [Cost Code]1)

    Try it out :)

    Dr. St Nicholas Burrus DHA, PMP

    I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Blank cells will not affect your outcome.

     

    The Cost Code portion is where you are most likely going to need to troubleshoot.

  • I cannot use cell references because the cost code may not be in the same place, it needs to search the Job ID based on the number in that column, then sum up certain cost codes determined by the formula. Make sense?

  • Do you think it has something to do with it being a number? Should I reference the cost category? that is just text and not a number.

  • This is what I have now:

    =SUMIFS({Test-TMS Job Data Act Expenses}, {Test-TMS Job Data Job ID}, "216116", {Test-TMS Job Data Cost Code Desc}, "Materials")

  • And this returns a 0 value

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That should be working. Try removing the quotes from around the Job ID.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!