I have two google sheet tabs: Data and Progress. I want to count the number of observations for each data collector in the Data tab [using arrayformula and countifs] that satisfy the following conditions:

1. The data collector ID [Data!B2:B] in the Data tab is equal to the surveyor ID in Progress [C7].
2. The start date in the Data tab [Data!J2:J] is equal to or greater than the start date in the cell [J4] in the Progress tab.
3. The end date in the Data tab [Data!J2:J] is equal to or less than the end date in the cell [N4] in the Progress tab.
4. The outcome of each observation in the Data tab [Data!AB2:AB] contains any of the text: "Community/school sensitisation", "Stakeholder engagement e.g., sending letters to GES for approval", "Logistical arrangements for fieldwork", "Administrative tasks", "Others -- specify"

I used the command below (though I will prefer using arrayformula) but gets 0 even though some of the observations meet the conditions I indicated above.

=SUM(COUNTIFS(Movement_data!AB2:AB,{"Community/school sensitisation", "Stakeholder engagement e.g., sending letters to GES for approval", "Logistical arrangements for fieldwork", "Administrative tasks", "Others -- specify"}, Movement_data!B2:B,C7, Movement_data!J2:J, ">="&$J$4, Movement_data!J2:J, "<="&$N$4))

Can anyone please help me with how to use arrayformula and countifs to work around this?

CodePudding user response：

Try with query

=COUNTA(QUERY({Movement_data!A2:AM},"select Col2 where 
Col34 matches 'Community/school sensitisation|Stakeholder engagement e.g., sending letters to GES for approval|Logistical arrangements for fieldwork|Administrative tasks|Others -- specify' 
and Col2 = '"&$C$7&"' 
and Col10 >= DATE'"&TEXT($J$4,"yyyy-MM-dd")&"' 
and Col10 <= DATE'"&TEXT($N$4,"yyyy-MM-dd")&"' "))

query

CodePudding user response：

Thanks for your response. Here is how the sample sheet looks like. The formula will be placed in Column J. I would like the formula should populate for all cells starting with J7. The command you sent returns a value in one cell only. Is there a way this can be done to automatically return the values for cell J8 onward? btw, can this be done using app scripts?

Thanks once again for your help.

Best, Edward