2

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]1

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.

player0
  • 124,011
  • 12
  • 67
  • 124
alex
  • 23
  • 4
  • 1
    In the preview, the table looks fine, but here it's somehow weird. I made it with a markdown table. I don't know what's wrong with the parser. – alex Oct 04 '22 at 17:44
  • Alex, Don't worry. You did good formatting. Have a upvote! – TheMaster Oct 04 '22 at 17:59

2 Answers2

1

try:

=COUNTIF(FILTER(FILTER(C2:E4, C1:E1=A7), B2:B4=A6), "hol")

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
1

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
)
TheMaster
  • 45,448
  • 6
  • 62
  • 85