Sum cells with check boxes
I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula.
Add check boxes to worksheet
The following animated image shows you how to quickly insert and position a check box, then easily copy and paste it to cells below.
The picture above doesn't show you how to link check boxes and hide linked cell values, detailed instructions below:
Select cell B3. Go to tab "Developer" and and press with left mouse button on "Insert" button and then "Check boxes (form control)".
Draw a check box in cell B3. Remove check box text. Use arrow keys to position checkbox 1 px incrementally.
Press and hold with left mouse button black box in the bottom right corner of cell B3.
Drag down as far as needed, in this example to cell B10.
The following article shows you a template that allows you construct multi-level to-do lists:
Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
Multi-level To-Do list template
Link check boxes to cells
Press with right mouse button on on check box in cell B3, press with left mouse button on "Format Control..."
Press with mouse on Cell link: field and select cell A3, press with left mouse button on OK button.
Repeat this with check box in cell B4 and select cell link cell A4.
Now repeat this with remaining check-boxes in cell range B5:B10.
Hide values in cell range A3:A10
Select cell A3:A10. Press CTRL + 1.
Press with left mouse button on "Custom" category, see picture above. Type ;;; in type field, see picture above. Press with left mouse button on OK button.
Recommended article:
Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Show / hide a picture using a button
Build formula
Double press with left mouse button on cell C12. Type:
Recommended article:
The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]
Create an array formula, see instructions below.
- Press CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys
If you did this right the formula now has a beginning and ending curly bracket, like this: {=SUM(A3:A10*D3:D10)}
Don't enter these characters yourself, they appear automatically.
Check a few check boxes to verify that the formula is working.
If you don't like array formulas, use this formula:
Get excel *.xlsx file
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Copy selected rows (checkboxes) (2/2)
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]
Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
Add checkboxes to a sheet (1/2) [VBA]
In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image […]
Toggle a macro on/off using a button
This article demonstrates how the user can run a macro by press with left mouse button oning on a button, […]
Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
3 Responses to “Sum cells with check boxes”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Great Tutorial Oscar! I'm using the check box in an Excel sheet thanks to you. Just wondering, is there a way to multiply the values in column C, rows 1 through 5 by any value I chose? Say row 1, when checked, could be multiplied by a value of 2 and row 4 when checked multiplied by a value of 5. If I could add the ability to select "quantities of" for each row that'd be great.
Randal,
Yes, it is possible. If column D in the example above contains quantities the formula in cell C8 becomes:
=SUMPRODUCT(($B$1:$B$5=TRUE)*$C$1:$C$5*$D$1:$D$5)
Good stuff! A couple of questions: I am trying to sum the number of checked boxes in a row in excel. I have quite a few and want to avoid having individual format controls for each cell. Is there an easier way to do this? Is there a way to copy and paste format controls across multiple cells?
thanks