Jan 14 2021 11:16 AM
I've created an Excel spreadsheet for a customer that has multiple jobsites. Each jobsite has multiple open invoices with the sum of all the invoices at the bottom. I would like to check a checkbox once the invoice is paid and have the amount of that invoice subtracted from the total. How do I go about doing this?
Jan 14 2021 03:17 PM - edited Jan 14 2021 04:01 PM
@jadriano24 Instead of a checkbox, you can create a helper column to track what's been paid and use the SUMIF function to control what's being summed. If you can share your workbook, without any sensitive data, we can create a working template
Jan 18 2021 06:56 AM
@adversiThanks for your response. I actually figured out what I needed to do through some trial and error formulas. I used Format Control to link the checkboxes to reference cells (way off to the side of the worksheet) so that when the checkbox is checked, it will return TRUE, and if cleared it will return FALSE. Then I used the SUMIF function to do the calculations when the checkbox is checked.
But, I am curious as to know how you would have done it. I've attached a sample file and would love your input.
Jan 18 2021 10:21 AM
@jadriano24 Your solution looks great! My suggestion follows the same concept, using a binary output that can be used for a SUMIF formula, more focused on a data entry level.
Jan 20 2021 07:44 AM
@adversi I'll keep your suggestion in mind for the future. Thanks for reaching out.
Enjoy the rest of your day.