I am currently attempting to find the ideology score for New York State Senate Members using

CodePudding user response：

You are loading too many times each Bill n° (63 * 2 = 126). Load one time each Bill n° in a specific tab, then apply the search in the relative tab with indirect function. I have prepared 2 Bill n° in this spreadsheet.

for the count use this in A6:

=INDEX(IF(B6:B="",,MMULT(IFERROR(REGEXMATCH(C6:100&"", "Y"), 0)*1, 
 SEQUENCE(COLUMNS(C6:100))^0)))

demo sheet

final sheet

fun fact lol:

total bills:             471
total sponsorships:     1670 out of 29673
total no-sponsorships: 28033 out of 29673