Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Progressive sumif formula for calculate invoice progress
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.