Google Sheets – countifs across rows

Question

I have a schedule that my team fills out daily in a google sheet. On a seperate tab, I would like a running count per day per schedule code per agent.

Linking a sample spreadsheet here. In this example, I'm trying to input a countif that returns

2019-01-27  T   5   6   0   4
2019-01-27  C   3.5 0   0   7
2019-01-27  LC  0   0   0   0
2019-01-27  S   0   0   0   0
2019-01-27  L   0.5 0   0   1
2019-01-27  M   0.5 0   0   1
2019-01-27  SP  0   0   0   0
2019-01-27  U   0   0   0   0
2019-01-27  MCX 2   0   0   2
2019-01-27  OCX 0   0   0   0
2019-01-27  TR  0   0   0   0

But I cannot for the life of me get a countifs function to work. Any help is much appreciated!

https://docs.google.com/spreadsheets/d/1gp0ZrcYLJfEnUHxgxagAl99X_MCjEIdvwFyfSdGngSE/edit?usp=sharing

Answer 1

Combine INDIRECT with MATCH:

=COUNTIF(INDIRECT("'Mon 1/27'!F"&MATCH(D$1,'Mon 1/27'!$A$1:$A$5,0)&":AG"&MATCH(D$1,'Mon 1/27'!$A$1:$A$5,0)),$B2)

INDIRECT

MATCH

Here is how it works:

1. MATCH(D$1,'Mon 1/27'!$A$1:$A$5,0) will search the row number of the agent, referenced to cell A1
2. INDIRECT("'Mon 1/27'!A"&MATCH(D$1,'Mon 1/27'!$A$1:$A$5,0)&":AG"&MATCH(D$1,'Mon 1/27'!$A$1:$A$5,0)) will return a range referenced always to columns F and AF but with the row number returned in step 1, ie, F3:AG3,F4:AG4, and son.
3. COUNTIF will just count the criteria in the range from step 2.

Hope this helps.

IMPORTANT: In the expected output you posted, the MCX result for Barack Obama is 2, but my formula gets 4. Are you sure your output is right?