Sum across many rows with transposed formulas google sheets

Question

I'm trying to build a formula that will count the total number of classes happening within a date range. This is the formula I can use, but it needs to include hundreds of rows (i.e. 'Class Counts' from B2 until B500 or so). Is there any way to turn this into an array so I don't have to have a formula that is pages and pages long?

=countifs(transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2))),">="&'All Totals'!$N4,transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2))),"<"&'All Totals'!$N5)+
countifs(transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3))),">="&'All Totals'!$N4,transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3))),"<"&'All Totals'!$N5)+
countifs(transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4))),">="&'All Totals'!$N4,transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4))),"<"&'All Totals'!$N5)
+ ... etc.

'All Data' column A contains the dates of the class, and column B contains the class names (which are repeated for each student but must only be counted once). 'Class Counts' column B contains the list of unique class names. 'All Totals' cells N4 and N5 contain the month starts to check between.

The goal is to count each occurrence of a class once if and only if it falls within the data range specified by N4 and N5 on 'All Totals'. The only issue is that there will eventually be hundreds of classes across many years.

My idea was to turn it into an array formula and count across the entirety of the ranges, but all my attempts returned a count of 0.

I can't share the actual sheet as it has personal information in it, but I created a test version here: https://docs.google.com/spreadsheets/d/1Nf0f5Bnuwe0-dnH6zHILGntdv2JDywFOTvmTjteXVLQ/edit?usp=sharing

The formula I'm trying to fix is on the IMPORT tab.

EDIT: I realized the "transpose" aspect of this was probably not necessary, so I edited the formula down a bit to this, but still can't sum automatically across all of the 'Class Counts' class names (column B).

=countifs(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2)),">="&'All Totals'!$N4,unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2)),"<"&'All Totals'!$N5)
+countifs(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3)),">="&'All Totals'!$N4,unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3)),"<"&'All Totals'!$N5)
+countifs(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4)),">="&'All Totals'!$N4,unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4)),"<"&'All Totals'!$N5)
+ ... etc

Thank you!

I've cleared out the private info so you can see the sheet here: docs.google.com/spreadsheets/d/… The formula I'm trying to fix is on the IMPORT tab. — Brian - RGY Studio

player0 player0 · Accepted Answer · 2020-05-23T19:34:40

try:

=QUERY('All Data'!A2:B, 
 "select B,count(B) 
  where A >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and A <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"'
  group by B 
  label count(B)''")

if you want only specific classes try:

=QUERY('All Data'!A2:B, 
 "select B,count(B) 
  where A >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and A <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"'
    and B matches '"&TEXTJOIN("|", 1, 'Class Counts'!B2:B)&"' 
  group by B 
  label count(B)''")

and to return only sum of it:

=SUM(QUERY('All Data'!A2:B, 
 "select count(B) 
  where A >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and A <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"'
    and B matches '"&TEXTJOIN("|", 1, 'Class Counts'!B2:B)&"' 
  group by B 
  label count(B)''"))

regex parenthesis fix:

=INDEX(SUM(QUERY(UNIQUE('All Data'!A2:B), 
 "select count(Col2)
  where Col1 >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and Col1 <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"' 
    and Col2 matches '"&
 SUBSTITUTE(SUBSTITUTE(TEXTJOIN("|", 1, 'Class Counts'!B2:B), "(", "\("), ")", "\)")&"' 
  group by Col2
  label count(Col2)''")))

Sum across many rows with transposed formulas google sheets

1 Answers

regex parenthesis fix: