# Formula Needed

Options
✭✭✭✭

Good Afternoon Community. I am hoping someone can assist me. I am trying to create a sheet that will sum billing amounts by job number from another sheet. On the source sheet we bill jobs out each month. So each job will have several billings. On this sheet I am creating I need those monthly billings to be totaled into one lump sum of them all. I tried this formula, but keep getting errors. If anyone can assist I would be most grateful. I am attaching screenshots of the two sheets. The first is the one I am creating and the second is the source sheet.

=SUMIFS({Billing Log Labor Billed}, {Billing Log Job #},[Primary Column]@row,"")

• Overachievers Alumni
Options

@Micah Turner you are missing your second criteria range. Assuming you are looking for "" as a criteria.

SUMIFS is range, criteria range 1, criteria 1, criteria range 2, criteria 2

You have range, criteria range 1, criteria 1, ""

So you either need to add a range before the "", or remove the ""

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• Overachievers Alumni
Options

Or I guess it's Primary Column in your collections sheet, not Job #, so

IF([Primary Column]@row<>"",SUMIFS({Billing Log Labor Billed}, {Billing Log Job #},[Primary Column]@row)

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• Overachievers Alumni
Options

@Micah Turner you are missing your second criteria range. Assuming you are looking for "" as a criteria.

SUMIFS is range, criteria range 1, criteria 1, criteria range 2, criteria 2

You have range, criteria range 1, criteria 1, ""

So you either need to add a range before the "", or remove the ""

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• ✭✭✭✭
Options

That did it! Thank you tremendously!

• ✭✭✭✭
Options

One more question.... If the Job number is blank it is returning a huge number from somewhere. How do I have it be blank if there is no Job number in that slot?

• Overachievers Alumni
Options

@Micah Turner it's because it looks like you have a large number of blank rows in your job list screenshot, so it's finding those empty job numbers. You could add an IF statement to your formula:

IF([Job #]@row<>"",SUMIFS({Billing Log Labor Billed}, {Billing Log Job #},[Primary Column]@row)

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• Overachievers Alumni
Options

Or I guess it's Primary Column in your collections sheet, not Job #, so

IF([Primary Column]@row<>"",SUMIFS({Billing Log Labor Billed}, {Billing Log Job #},[Primary Column]@row)

BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

SEATTLE WA, USA

IRON MOUNTAIN

• ✭✭✭✭
Options

That did it. Thank you Again!