『COUNTIFSにHLOOKUPを合わせたいです』（ゆきな）

色々調べたのですが、どうしてもわからないので教えてください。
出勤簿を集計するシートを作成しています。

縦が社員名簿（部署別）、横が日付となっている入力シートに「有休」「出勤」などをプルダウンで入れてもらったシートが地域別に３シートあります。

それらを出勤管理簿シートで日付、部署（３シート分を縦に羅列）毎に、○日は全社で有休が○人、出勤が○人と分かるように集計したいのです。

集計シートは縦が3シート分の部署名を羅列して、有休、出勤、テレワークに区切っていて（部署名は3回でてくるイメージ）、横が日付です。

=countifsの条件１で部署名と合致するものカウントし、条件２で「有休」などの項目に合致するものを集計するところまで出来たのですが、これにHLOOKUPで日付の条件を追加したいのですが、どうすればよいのかわかりません。
私の希望としては集計シートの日付の列を「HLOOKupで見に行って、その列にある部署名が同じ、かつ「有休」の人をカウントしたいのです。

=COUNTIFS('北海道ローテーション’!$B$10:$B$200,$C79,'北海道ローテーション '!AL$10:AL$200,"有休")

こんな式です。「北海道ローテーション」というのが入力シートの名前です。

わかりづらくて申し訳ございませんが、他の関数を使う方法でも構いませんので教えていただけますと幸いです。
よろしくお願い致します。





＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
----
 ちょっと確認

 部署名が B10:B200 というのは想像できますけど
 日付範囲はどこからどこまで？

 そこから特定の日付だけを集計したいってこと？
 だとして条件の日付はどこで指定するんですか？

 とりあえず以上です
（笑） 2020/04/29(水) 12:27
----
 集計シートも横に日付が並んでるんですね。

 ・地域別のシートと集計シート、それぞれの日付範囲は？
 ・地域別シートと集計シートで日付が違うんですか？

 以上です
（笑） 2020/04/29(水) 12:49
----
ご回答ありがとうございます。

 >そこから特定の日付だけを集計したいってこと？
　はい。そうです。
 
>だとして条件の日付はどこで指定するんですか？
　どちらのシートも横列が日付になるので、集計シートの先頭行と、入力シート（ローテーションシート）
　をHlookupで照合したかったのですが･･。わかりにくいですよね。画像を貼りたかったのですが、うまく
　いかなくて･･すみません。

（ゆきな） 2020/04/29(水) 14:51
----
・地域別のシートと集計シート、それぞれの日付範囲は？
'北海道ローテーション '!AI$3:BD$200　です。
　首都圏、九州とそれぞれローテーションシートはありますので
　そこは式を組みなおさなければいけません。

>・地域別シートと集計シートで日付が違うんですか？
日付は同じです。どちらも5月分になります。

（ゆきな） 2020/04/29(水) 14:55
----
↑
すみません。間違えました。

・地域別のシートと集計シート、それぞれの日付範囲は？
'北海道ローテーション '!AI$3:BD$3　です。
（ゆきな） 2020/04/29(水) 14:56
----
 ＞'北海道ローテーション '!AI$3:BD$3　です。

 有休とかを入力するのは10行目からってこと？

 地域別シートと集計シート、同じ日付が同じ順番で並んでいるのなら
 検索するまでもないですよね。
 ↓ を右・下コピーでできませんか？

 =COUNTIFS(北海道ローテーション!$B$10:$B$200,$C79,北海道ローテーション!AI$10:AI$200,"有休")

 以上です
（笑） 2020/04/29(水) 16:17
----
笑さま

ご回答いただき、ありがとうございます。

そうなんです。1セルだけならそれで大丈夫で、それを横に引っ張ってコピー（オート）すれば
いいですよね。

ところが、2シートとも同じ日付が同じ順で並んでいるのですが、集計シートの方が、2列1セルに結合されていて「有休」の人をカウントする人数の列と、出社率を出す％の列があるせいで、横にビーっとコピーすると参照セルがずれてしまうのです。

5/1が、AI$10:AI$200、それを横にコピーすると隣の5/1の％を求める列にAJ10:AJ200と入ってしまい、5/2の列には本来AJ10:AJ200が入って欲しいのにAK10:AK200となってしまうのです。

4月は全部手動で参照セルを書き換えたのですが、5月は日にちが多くてこれは大変すぎるなと思って。。

そこでLOOKUPを使おうと考えたのですが、参照を1列飛ばしにすることができれば、確かに検索する
までも無いです。

意味が分かりますでしょうか･･?
もし、良い方法があったら教えてください。

何度も申し訳ありません。

（ゆきな） 2020/04/29(水) 19:40
----
 ＞集計シートの方が、2列1セルに結合されていて「有休」の人をカウントする人数の列と、
 ＞出社率を出す％の列がある

 出社率も数式？
 それについては何も問題ないんですか？

 =COUNTIFS(北海道ローテーション!$B$10:$B$200,$C79,INDEX(北海道ローテーション!$AI$10:$BD$200,0,ROUNDUP(COLUMN(A1)/2,0)),"有休")

 右のセルには出社率の式を入れる。
 2つのセルを選択して右・下コピー
 ~~~~~~~~~~~~~~~~~~~
 ※式の最後の方、COLUMN(A1) はA1セルの値を参照しているわけではないので変更しないように

 以上
（笑） 2020/04/29(水) 23:57