Progressive sumif formula for calculate invoice progress

kdeen

Over the course of a contract, I want to track how much has been invoiced and how much is left. If possible, for some contracts I know the invoicing is on a cycle and should be the same amount on say a monthly basis.

I have columns for each month listing the invoice amount Invoice Amount and a date column to be completed when it's sent Date Invoice Sent. I also have a Total Contract column, and a Total Invoiced column which I want to show the amount invoiced so far. I want to be able to fill out all the invoicing amounts for the year, but have the Total Invoiced column SUM only the Invoice Amount column if the Date Invoiced Sent column is filled in. I know you can do SUMIF formulas with check boxes so I added checkbox columns for each month with formulas to mark themselves as "Complete" or "Checked" when the Date Invoice Sent is not blank. 

Is there a way to build a nested SUMIF formula that accounts for dependencies of the checkbox column, so if only the first four checkbox columns are complete, it will disregard adding values entered in future Invoice Amount columns.

If not, I can always just fill in the invoice amounts when it's time to invoice but thought I'd try to planning out and automate tracking the progress if possible. 

J. Craig Williams

Use SUMIFS. You won't need the checkboxes, as the dates can be the criteria.

I'd need to see the data arrangement to provide a more detailed formula, but it is pretty easy.

Also: My Best Practice Tip - don't use SUMIF. Always use SUMIFS. It is easier to expand to two or more criteria from one criterion. 

Craig

kdeen

Thanks Craig,

The setup is as follows:

Columns

Total invoiced

Remaining to invoice

Invoice 1 due date

Invoice 1 amount

Invoice 1 date sent

 

Then I have the set of three invoice columns for 12 months. I'd like the "total invoiced" column to add up the invoice amounts only after the invoice sent date is filled in. This way I can plan out the amounts and due dates then fill in the date sent as they are actually sent and see the progress to make sure our invoicing stays on track. Would the SUMIFS look for the Date Sent to be not blank, or be in the past? Then I would need it to capture this for every column, if the corresponding Date Sent column is filled it, it will add the corresponding Invoice Amount (when Invoice 2 Date Sent is complete, it will add the amount for Invoice 2 Amount), when Invoice 3 Date Sent is complete, it will add the amount for Invoice 3, etc.)

 

J. Craig Williams

No, SUMIF or SUMIFS won't work for this situation, at least not in the way you have the columns arranged.

The reason is that the way the ranges work, you'd be looking at date columns for Due Date and for Sent Date, when really, you only care about Sent Date.

If you rearrange your columns to be [Invoice Amount #] (all 12), then [Invoice Sent Date #] (all 12) and so on then this would work:

=SUMIFS([Invoice Amount 1]1:[Invoice Amount 12]1, [Invoice Sent Date 1]1:[Invoice Sent Date 12]1, NOT(ISBLANK(@cell)))

This is summing the 12 columns for the Amount if there is a value (any) in of the Sent Date columns.

This might be slightly better:

=SUMIFS([Invoice Amount 1]2:[Invoice Amount 3]2, [Invoice Sent Date 1]2:[Invoice Sent Date 3]2, ISDATE(@cell))

as it checks instead for a valid date (so you could put something like "missed" in the date cell) -- it depends on what you want -- I can see use cases for a non-date being summed or not.

The same could be done without rearranging the columns but unless there is an amazingly strong reason to keep them the way they are, I wouldn't bother (the formula would be a string of IF() + IF() + ... statements.

I hope this helps.

Craig

 

 

kdeen

Perfect, thank you!