## More Spreadsheet Wisdom

It’s the simple things that make a difference. How to sum a column of checkboxes. It seems like an easy enough task. Well, it is, I suppose, once you know how. Naturally, I thought I would just be able to use a simple sum function, but that returns an error. In a previous post I outlined how to use an if/then statement. A similar approach is needed if we are to sum a column (or row) of checkboxes, but beware; applying exactly the same principles will produce an error, or at least with my limited knowledge it did. The if/then statement we need to sum checkboxes reads like this: if a cell is true, then this, if it isn’t, then this. I was unable to write one all encompassing equation so I ended up writing it again and again and just summed all the statements together. If we have checkboxes from B1 to B3 and we wanted the sum of the checked boxes, it would look something like this:

=IF(B1,1,0)+IF(B2,1,0)+IF(B3,1,0)

Now if someone can leave a comment below as to how to go about writing a function that says “Sum B1 *through* B3 if they are true” I would be much appreciative.

***UPDATE: Well that was fast. Thanks goes out to Andrew Eller for providing the following function that more easily sums a range of checked boxes:

=COUNTIF(B1:B3,true)

I’ve already replaced my tedious 30+ polynomial-esque function with this much more efficient way of summing checked boxes. Congratulations Andrew! You win…well, you have my respect.***

=IF(AND(B1:B3),COUNTA(B1:B3),0)

If b1 through b3 are all true, count them, else give value of zero.

Have you tried this one yourself? I wasn’t able to get it to work.

Works in numbers….

But it only counts if every checkbox is checked. When I first read your post I thought that was what you needed.

You rock my spreadsheet world

=COUNTIF(B1:B3,true)

Counts the instances of true within the range of B1 through B3.

This is the function I was looking for! Simple and intuitive. Thank you Andrew! I’ll update the post with this newfound knowledge. Feel free to drop these jewels of nerdery on DFJ anytime :)