SUMIFS Cross Sheet Issue

ryan.diamond55371

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

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

Paul Newcome

Blank cells will not affect your outcome.

 

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

ryan.diamond55371

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?

ryan.diamond55371

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.

ryan.diamond55371

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")

ryan.diamond55371

And this returns a 0 value

Paul Newcome

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