SUMIFS formula for invoiced to date and to be invoiced

Chris Wolfe

I have 3 cells in one row. Each contains a percentage of the total amount of the line to be invoiced. We invoice the first 25% at initial submittal to the client, 50% at the 2nd stage between 30-90 days later, and the final 25% 7-30 days later. I need a formula to add the 3 cells together if the corresponding invoice column is not blank (i.e. the first 25% & 50% are invoiced and the invoice date is filled in but the final 25% is not i need to sum the first two cells. If only the first 25% is invoiced i just need that one cell)

I've tried sumif, sumifs, and sum with individual if formulas but I can't seem to get it to work. any help is appreciated

Paul Newcome

Have you tried just a basic SUM function. It should ignore the empty cells (it treats them as zero and adding zero to a number doesn't change anything).

Chris Wolfe

https://community.smartsheet.com/discussion/comment/291593#Comment_291593

@Paul Newcome,

I need it to only sum a value if the corresponding invoice date cell has date in it. The below is an example of the results I am looking for manually completed. I need a formula to do it for me so I can include it in a report and it is constantly updated as pieces are invoiced

Paul Newcome

Ah. Ok.

We will start with an IF statement to say that if the first date cell is not blank, then output the amount, otherwise output zero. Then we duplicate this IF for the second and third. Then we add them together.

=IF([Invoice#1 Date]@row <> "", [Invoice#1]@row, 0) + IF([Invoice#2 Date]@row <> "", [Invoice#2]@row, 0) + IF([Invoice#3 Date]@row <> "", [Invoice#3]@row, 0)