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

Select cell B3. Go to tab "Developer" and and click "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.

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

Link check boxes to cells

Right click on check box in cell B3, click "Format Control..."

Click on Cell link: field and select cell A3, click 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.

Click "Custom" category, see picture above. Type ;;; in type field, see picture above. Click OK button.

Build formula

Double click cell C12. Type:


Create an array formula, see instructions below.

  1. Press CTRL + SHIFT simultaneously
  2. Press Enter once
  3. 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:


Download excel *.xlsx file

Sum using checkboxes.xlsx

Sum formula examples

Sum values where adjacent cell value equals a criterion
This post demonstrates how to sum adjacent values using a condition.
Learn how to use the SUM function and it's arguments, many examples and a file to download.
Sum only if unique value in adjacent column
This post explains how to construct a formula that adds values if adjacent value is unique.
Sum values between two dates with criteria
Explains how to build a formula that adds values between two dates.