0

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

Chart of characters. In Column A characters' names are listed. Column B is the one I need help with. Columns C-M are checkboxes representing the types of magic the characters can use.

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!

2 Answers 2

0

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

=byrow(C3:M,lambda(chekbox,if(counta(chekbox)=0,,countif(chekbox,true))))
5
  • So much simpler than mine :-) I love seeing your solutions. Figured there must be a way to specify checkboxes from the get go.
    – Blindspots
    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.
    – Blindspots
    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 :-)
    – Blindspots
    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.
    – Daniele
    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!
    – Blindspots
    Oct 11, 2022 at 20:08
0

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

  =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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.