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