Formula Needed

Micah Turner

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

Brian_Richardson

@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

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

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

Micah Turner

That did it! Thank you tremendously!

Micah Turner

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?

Brian_Richardson

@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

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)

Micah Turner

That did it. Thank you Again!