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: https://docs.google.com/spreadsheets/d/1Nf0f5Bnuwe0-dnH6zHILGntdv2JDywFOTvmTjteXVLQ/edit?usp=sharing The formula I'm trying to fix is on the IMPORT tab. — Brian - RGY Studio, May 23 '20 at 17:44

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

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)''")))

edited Jun 20 '20 at 09:12

Community

1
1

answered May 23 '20 at 19:34

player0

124,011
12
67
124

Thanks for the ideas. One problem came up though. It doesn't seem to return the correct sum. It is only summing those classes, but it's counting duplicates. It needs to ignore classes with the same name that happened on the same date in the count. Any ideas? – Brian - RGY Studio May 23 '20 at 20:13
sure, try: `=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 '"&TEXTJOIN("|", 1, 'Class Counts'!B2:B)&"' group by Col2 label count(Col2)''")))` – player0 May 23 '20 at 21:02
That is incredibly close to working. For some reason, it isn't counting one of the classes. The class name is "Senior Musical Theatre Routine (Weekly Pre-Recorded Classes)". Is that perhaps an issue because of the special characters in the class name ("-" or "()")? If so, do you have one last idea to correct for that type of issue? In case it's easier, the goal is to filter out any classes that are either blank, or start with "Private". That's what the list on 'Class Counts' is doing. Thank you for your help with this! – Brian - RGY Studio May 23 '20 at 22:05
try: `=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)''")))` – player0 May 24 '20 at 01:50
That did it. Thank you! If you want to add this to your answer, it would be great to explain that last edit, but either way, I can accept it now. – Brian - RGY Studio May 24 '20 at 02:43

Sum across many rows with transposed formulas google sheets

1 Answers1

regex parenthesis fix: