• Amit Sarda

Summing check-boxes in Google Sheets

Introduction


Google Sheets allows you to change a column data-type to check-boxes for easier data entry. However, it poses a few challenges if you're trying to use this data for further analysis in Google Sheets.


Why is it difficult to work with checkboxes in Google Sheets?


The reason is simple: not many people know how this data is stored in the backend. Also, changing the formatting of these columns removes the checkboxes from the view. Although it makes it easier to enter data, it is not easy to model further analysis of these data columns that have check-boxes.


A case study


The following version of a Google Sheets document illustrates this problem: a simple attendance management system. Column A has dates, while columns B to F have names of students. Attendance is marked using check-boxes in columns B to F.


The challenge here is to find the number of students who attended the class on any given day. The last column - 'Total' - indicates the total number of students who attended the class on that particular day.


Without understanding how the data is stored behind a cell with a check-box, it is difficult to create any formulas that would give us the answer.



Unfolding the mystery behind check-boxes


Check-boxes are stored as boolean values (TRUE or FALSE) in Google Sheets. So the trick is to treat these cells as if they contain TRUE or FALSE values and then model the analysis accordingly.


In this case, imagine columns B to F to have an array of TRUE and FALSE values. You can then use this array as one input in the SUMPRODUCT formula and use an array of 1s as the second input. The number of 1s in the second input will be the same as the number of students - in this case, 5.


=SUMPRODUCT(B2:F2,{1,1,1,1,1})



#googlesheets #checkboxes #arrayformulas

 
  • Twitter
  • Instagram

©2020 by Amit Sarda