Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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))))
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.
INDIRECT(SUBSTITUTE(ADDRESS(MAX(XMATCH)))&MATCH :-)
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
=ADDRESS(row, column, [absolute_relative_mode])
=ADDRESS(4,7,4)
=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(
ADDRESS(4,max(XMATCH(true,A4:4,,-1),
XMATCH(false,A4:4,,-1))),"4","")
&MATCH("zzzz",A:A)),
lambda(r,countif(r,"=true")))
// One liner
=byrow(INDIRECT("C4:"&SUBSTITUTE(ADDRESS(4,max(XMATCH(true,A4:4,,-1),XMATCH(false,A4:4,,-1))),"4","")&MATCH("zzzz",A:A)),lambda(r,countif(r,"=true")))
See it in action, Sample Google Sheet I have attached a sample spreadsheet so you can see how it works:Google Sheet with Sample Data and Formulas
