# Array Formula Countif Checkboxes

I'm working on a spreadsheet (in google sheets) for tracking characters' magical abilities.

In column b I want to have a count of how many boxes are ticked. Right now it's just the same formula =countif(c3:m3,true) copied and pasted, but I want to have it automatically apply to new rows, like an array formula would do. I've been trying to teach myself how to do various things on google sheets, but I'm still very new, and can't figure this one out. Any help is appreciated!

Try in `B3` the new functions `byrow()` and `lambda()` like this:

``````=byrow(C3:M,lambda(chekbox,if(counta(chekbox)=0,,countif(chekbox,true))))
``````
• So much simpler than mine :-) I love seeing your solutions. Figured there must be a way to specify checkboxes from the get go. Oct 11, 2022 at 8:33
• I now remember why I created the range using `INDIRECT`. Adding a condition with `COUNTA` to avoid zero values down the column would trigger the `#ERROR Calculation limit was reached while trying to compute this formula.` It should work but doesn't seem to for me. I plugged your solution into a duplicate sheet Test for Danielle and it still triggers the error. If you get a minute take a look and let me know what you think. Oct 11, 2022 at 10:40
• Did some testing and it seems that the number of rows needs to be limited to ~28,500 or less. Not sure if this changes depending on the number of columns populated with data or not. This seems to be what is triggering the the error. I'm not sure what that reflects under the wrapper in terms of function efficiency (erroring out above 28,500 rows for a data set that is only 24 rows). I'm inclined to generate the range dynamically to match the data. I bet you have a more efficient way than my `INDIRECT(SUBSTITUTE(ADDRESS(MAX(XMATCH)))&MATCH` :-) Oct 11, 2022 at 10:55
• in your test sheet you have set over 50,000 rows and clearly it is easy to reach the limit for that calculation. I modified it with only 1000 rows and it works easily even in the C4:Z range. Oct 11, 2022 at 19:46
• Yes, apparently even 28,500 will work. :-) I'll keep that in mind next time. These are defaults so I imagine others will trip over it. ty! Oct 11, 2022 at 20:08

### Count Checked (True) Values

``````Using my sample data see link to sheet

// Basic formula

=byrow(range,lambda(r,countif(r,"=true")))
=byrow(C4:G24,lambda(r,countif(r,"=true")))
``````

Rather than hard coding the range, or alternatively iterating over all the rows and columns, I added some formulas to calculate the size of the data range.

Last Row

``````  // calculate last row (last name)

=MATCH("zzzz",A:A)
=24       // last_row
``````

Last Column
I'm sure someone can pare this down. I used XMATCH and looked for location of the last "true" value and the last "false" value, then used MAX to take the largest of the two to determine the last checkbox in the row.

FYI row 4, range(A4:4) is the first data row in my sample table.

``````  // Calculate last column (checkboxes) of data

=max(XMATCH(true,A4:4,,-1),XMATCH(false,A4:4,,-1))
=MAX(7,2)
=7     // last_column ('G')

``````

Convert the column # to a letter

``````  // Use ADDRESS to get A1 Notation

=G4

// Remove row and keep last_column letter

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
=SUBSTITUTE("G4", "4", "")
=G
``````

Create the range

``````  // use INDIRECT to build range

=INDIRECT(cell_reference_as_string, [is_A1_notation])
=INDIRECT("C4:" &last_column &last_row)
=INDIRECT("C4:"&"G"&"24")
="C4:G24"
``````

Combine it all
This allows the range to be dynamic based on the number of columns you create and number of rows of people you add.

``````  // Broken out for ease of reading.

=byrow(INDIRECT("C4:"
&SUBSTITUTE(