I work on a google sheet, where I should see the number of holidays per day for each team.
The table looks like this:
[table]
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee Name | Team Name | 01/03/2022 | 02/03/2022 | 03/03/2022 |
| 2 | Employee 1 | Team A | hol | hol | hol |
| 3 | Employee 2 | Team B | early | early | early |
| 4 | Employee 3 | Team A | hol | late | late |
| 5 | |||||
| 6 | Team A | ||||
| 7 | 01/03/2022 | ||||
| 8 | RESULT HERE |
I want to have a result that tells me that Team A had on 01/03/2022 2 holidays.
=countif(query(A1:E4,"select C where B contains '"&A6&"'" ),"hol")
A6 contains the team I am looking for.
A7 contains the date I am looking for.
A8 should show me the number of hol.
Currently, I have a fixed column to look inside which is "C". I want to replace that with the date from A7 - How do I do that? I tried to play around with transpose and filter but had success.
CodePudding user response：
try:
=COUNTIF(FILTER(FILTER(C2:E4, C1:E1=A7), B2:B4=A6), "hol")
CodePudding user response：
Use XMATCH to get the Col number for QUERY and count inside query instead of COUNTIF:
=QUERY(
{B1:E4},
"Select count(Col1)
where Col"&XMATCH(A7,B1:E1)&"='hol'
and Col1='"&A6&"'
label count(Col1) ''",
1
)