Discover how to sum certain values from checked boxes in Excel 365.
This method uses the double dash and the function SUMPRODThe result is simple, reliable and quick to implement.
The boxes checked: TRUE or FALSE
A checkbox in Excel 365 returns TRUE or FALSE depending on its state, checked or unchecked. These values are not numeric, which prevents Excel from performing a direct addition. To work around this problem, the use of double dash will convert TRUE and FALSE into 1 or 0.
--VRAI→ 1--FAUX→ 0
This conversion is the key to the calculation in our example
Why use SUMPRODUCT
Function SUMPROD It multiplies two ranges of the same size, then adds the results of each multiplication. This is a reliable function for advanced calculations, such as... income tax calculation.
Here, SUMPRODUCT is written by specifying only two data ranges:
- The amounts in the column headers, which will be stuck with dollars.
- The checked boxes converted into 0 and 1.
The formula for adding the amounts
Therefore, we simply need to take into account the cell ranges
- namely those containing the amounts to be added together,
$C$2:$F$2(dollars are very important in our case) - and those containing the 4 checkboxes
--(C3:F3)The use of parentheses is mandatory; otherwise, the double hyphen cannot perform the transformation between TRUE and FALSE.
Please note: If your amounts are not in your table, you can construct the matrix of numbers by replacing $C$2:$F$2 by {10.20.30.40}
Why this method works
SUMPRODUCT simply calculates Value × 0 ou Value × 1The formula then adds up all the non-zero values. The result is exact and immediate.
Conclusion
The calculation may seem complex with checked boxes. However, with the double hyphen and SUMPRODUCT function, the solution becomes simple, quick, and clear. This method is suitable for all your bonus, premium, or validation tables.
Another way to proceed was given in the comments by Pierre. His solution consists of writing only one argument which contains the multiplication of the 2 cell ranges. =SOMMEPROD($C$2:$F$2*C3:F3)
15/11/2025 @ 21:27 p.m.
If you use multiplication (product) in the argument, you can simply perform a sum.
SUMPRODUCT(A;B)
Sum(A*B)
14/11/2025 @ 23:09 p.m.
Hello Frédéric
I just came across your tip following your post on LinkedIn.
I really like the idea of doing calculations based on the checkboxes.
It seems to me that we could do it more simply without SUMPRODUCT and without the --
=SUM(C3:F3*$C$2:$F$2)
Pierre
15/11/2025 @ 07:56 p.m.
Yes, I hadn't thought of that. I'll add it to the article.