Google-sheets – Google Sheets countif attendance sheet, quarterly report

formulasgoogle sheets

I have a Google Sheet that I use to track attendance at an open lab that I run. Each quarter I put together a report where I count the number of people that attended that quarter. If I just use the count function I could include people that only attended the first quarter in the second quarter report or that attended the second in the third.

I want a way to say, "if this person attended between these dates then count them."
For example, in the picture I attached Name 2 only attended during the first quarter so they wouldn't be included in the second quarter count. Name 5 attended during both so they would be included in both counts.

My best idea for putting this into Google Sheets is
=countif(A2:A29,B2:Y2<>0)
or in other words count the names if they attended during the first quarter. Is there any way to do this?

Edit: Here's a link for the spreadsheet:https://docs.google.com/spreadsheets/d/1SwukY3SsjWfYyu2RakED0S0V4kZwRfNN0nVoDsKMN5A/edit?usp=sharing

Doing this manually there should be 38 people that attended during the first quarter and 42 that attended the second.

enter image description here

Best Answer

Here's a sample where I tried to solve the issue in tab "Result"

There's some additionnal columns for each quarter (A:A, B:B, C:C) then with some QUERY you can check if there has been an attendance or nah.

The formula in A3 :

=index(transpose(query({transpose(INDIRECT(A2))},"Select "&textjoin(",",TRUE,ArrayFormula(if(len($D$3:$D$100),"Max(Col"&ROW($D$2:$D$100)-ROW($D$2)+1&")","")))&"")),,2)

There is a main query with the range A2 for the first quarter: in this cell you can add the desired range, you can try a formula to automate this. (I.E. filter every date with the desired months ('=MONTH') then take the reference of the cells containing `MAX' and 'MIN' values)

The query select the max value of every row (the range has been transposed). You can also use dynamic range for the LEN and ROW formulas.

Then it just =TRANSPOSE the data. =INDEX is way to pick the second column (first column is fill with names)

You can now go to tab Clean for a simple =IF formula checking wether or not attendees where present during quarter/period 1

Edit : Credit for 3/4 of the formula

Related Topic