Excel checkbox

%3CLINGO-SUB%20id%3D%22lingo-sub-2061969%22%20slang%3D%22en-US%22%3EExcel%20checkbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061969%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20created%20an%20Excel%20spreadsheet%20for%20a%20customer%20that%20has%20multiple%20jobsites.%20Each%20jobsite%20has%20multiple%20open%20invoices%20with%20the%20sum%20of%20all%20the%20invoices%20at%20the%20bottom.%20I%20would%20like%20to%20check%20a%20checkbox%20once%20the%20invoice%20is%20paid%20and%20have%20the%20amount%20of%20that%20invoice%20subtracted%20from%20the%20total.%20How%20do%20I%20go%20about%20doing%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2062744%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20checkbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2062744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F926329%22%20target%3D%22_blank%22%3E%40jadriano24%3C%2FA%3E%26nbsp%3BInstead%20of%20a%20checkbox%2C%20you%20can%20create%20a%20helper%20column%20to%20track%20what's%20been%20paid%20and%20use%20the%20SUMIF%20function%20to%20control%20what's%20being%20summed.%20If%20you%20can%20share%20your%20workbook%2C%20without%20any%20sensitive%20data%2C%20we%20can%20create%20a%20working%20template%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2071037%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20checkbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2071037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3EThanks%20for%20your%20response.%20I%20actually%20figured%20out%20what%20I%20needed%20to%20do%20through%20some%20trial%20and%20error%20formulas.%20I%20used%20Format%20Control%20to%20link%20the%20checkboxes%20to%20reference%20cells%20(way%20off%20to%20the%20side%20of%20the%20worksheet)%20so%20that%20when%20the%20checkbox%20is%20checked%2C%20it%20will%20return%20TRUE%2C%20and%20if%20cleared%20it%20will%20return%20FALSE.%20Then%20I%20used%20the%20SUMIF%20function%20to%20do%20the%20calculations%20when%20the%20checkbox%20is%20checked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20I%20am%20curious%20as%20to%20know%20how%20you%20would%20have%20done%20it.%20I've%20attached%20a%20sample%20file%20and%20would%20love%20your%20input.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2071840%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20checkbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2071840%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F926329%22%20target%3D%22_blank%22%3E%40jadriano24%3C%2FA%3E%26nbsp%3BYour%20solution%20looks%20great!%20My%20suggestion%20follows%20the%20same%20concept%2C%20using%20a%20binary%20output%20that%20can%20be%20used%20for%20a%20SUMIF%20formula%2C%20more%20focused%20on%20a%20data%20entry%20level.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

4 Replies

@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

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

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

@adversi I'll keep your suggestion in mind for the future. Thanks for reaching out.

 

Enjoy the rest of your day.