エクセル countif関数で、範囲を可変にするには

countif関数を使い、D列の任意の値の個数を出したいと考えています。

検索範囲：D3～D列の最終行まで

なお、D列は空白セルもあるため、必ずデータが存在するA列の値を参照し
=COUNTA(OFFSET($A$3,,,COUNTA(A:A),1))
こちらでD列の最終行の値を求めることまではできました。

ここからcountif関数を使用する際
通常150行までであれば
=COUNTIF(D3:D150,1)
となりますが、このD150のところを可変にする場合の記述を
どのようにしたらよいでしょうか？

=COUNTIF(D3＆”:D”＆COUNTA(OFFSET($A$3,,,COUNTA(A:A),1)),1)
↑このような感じの雰囲気は分かるのですが・・・

よろしくお願いします。

質問者からの補足コメント

• 回答ありがとうございます。
  言葉足らずですみません。
  
  1行目に行見出しがあり、2行目に提示した計算結果を表示し、その2行を固定して
  画面のスクロールをする関係上、3行目以降のデータをカウントすることになります。
  
  イメージとしては
  
  氏名　　月　　火　　水　　木　　金　　土　　日
  　　　　10　　30　　40　　25　　５　　２　　3
  
  太郎　　１　　1　　　3
  次郎　　1　　　　　　1　　　　　　1
  三郎　　　　　1　　　2　　　1　　　　　1
  ・
  ・
  
  といった感じです。
  1と2の合計が2行目に、3についてはカウントしない、という仕組みです。
  countifで1と2を合計しようと考えていました。
  
  INDIRECT関数を使用すればよいとの助言がありましたので、
  その方向で考えてみたいと思います。
  もし、提示したデータでもっと簡単にできる方法があれば、助言いただけると幸いです。

    補足日時：2020/08/30 07:31

• 助言ありがとうございます。
  確かに
  =COUNTIF(B3:B1000,"<3")
  
  こちらであらかじめ広い範囲の設定であれば大丈夫なのですが、
  心配なのは、不要になった行を削除した際に、B1000 の範囲が減っていくことになりますよね？
  まぁ、最大値を設定してあればほぼ大丈夫なのですが、将来的に後任へ引き継いだ際に
  懸念される材料でして・・・

  No.5の回答に寄せられた補足コメントです。 補足日時：2020/08/30 17:49

No.6 ベストアンサー 回答者： tom04 回答日時： 2020/08/30 17:59

No.3・5です。

＞心配なのは、不要になった行を削除した際に

どうしても心配なのであれば
=COUNTIF(B3:B1048576,"<3")

のように現在のExcelの最終行までを指定しても構わないと思います。

＞将来的に後任へ引き継いだ際に
というコトですが、
単純な数式にする方が後任者も簡単に修正できるのではないでしょうか？

無理やり長い数式にする方が不親切のような気がします。
（当方の勝手な解釈かも・・・）m(_ _)m

この回答へのお礼

丁寧なやりとり、ありがとうございました。
＞単純な数式にする方が後任者も簡単に修正できるのではないでしょうか？

確かに、複雑な数式よりも簡単な方が、後任にもわかりやすいですね。
1000はあったとしても、10000までの行の削除は起こりにくいと
考えられるため、単純な関数式で対応したいと思います。

他の皆様方も回答ありがとうございました。
この場を借りてお礼申し上げます。

お礼日時：2020/08/31 00:15

No.5 回答者： tom04 回答日時： 2020/08/30 11:41

No.3です。

3行目以降は整数しか入らない！という前提であれば
わざわざA列で範囲指定しなくてもあらかじめ広い範囲を指定しておいてはダメですか？

=COUNTIF(B3:B1000,"<3")

こんな感じで。

範囲内に整数で「0」なども入る可能性がある場合は
=COUNTIFS(B3:B1000,">=1",B3:B1000,"<=2")

尚、どうしてもA列で最終行を取得し、それを利用したいのであれば
無理やりになりますが、
=COUNTIF(OFFSET(B3,0,,COUNTA($A:$A)-COUNTA($A1:$A2)+2),"<3")

といった感じでしょうかね。m(_ _)m

No.4 回答者： iothes00 回答日時： 2020/08/29 09:52

=MAX(IF(A1:A10000="",0,1)*ROW(A1:A10000))

上記を打ち込んでctrl+enter
とすると間に空白があっても最後になります

=COUNTIF(indirect("D3:D"&150),1)

indirectは範囲文字を通常の選択化します。

No.3 回答者： tom04 回答日時： 2020/08/29 09:43

こんにちは！

＞=COUNTA(OFFSET($A$3,,,COUNTA(A:A),1))
＞こちらでD列の最終行の値を求めることまではできました。

ん？これで
＞こちらでD列の最終行の値を求めることまではできました

「最終行」が取得できた！というコトでしょうか？
A3セル以降空白セルがない！という前提であれば

=COUNTA(OFFSET($A$3,,,COUNTA(A:A),1))+2
だと思いますが・・・

ただ、質問分を拝見するとD列の中に「1」のセルがいくつあるか？を知りたい！
というコトのようなので、単純に
=COUNTIF(D:D,1)

だけでいいと思うのですが・・・

なぜ最終行を指定してCOUNTIF関数を使わないといけないのか不明ですが、
どうしても！というコトであれば
=COUNTIF(INDIRECT("D3:D"&COUNTA(OFFSET($A$3,,,COUNTA(A:A)))+2),1)

でいけると思います。m(_ _)m

No.2 回答者： 銀鱗 回答日時： 2020/08/29 09:38

OFFSET関数や、INDIRECT関数を使いましょう。

OFFSET関数は範囲を数値で指定できます。
INDIRECT関数は、文字列を範囲に変換する関数なので、範囲を示す文字列を作って与えれば良い。

それぞれの関数の使い方を調べてみましょう。
意外と簡単ですよ。
まあ、質問者さんの場合はINDIRECT関数で解決するような気もしますけど...。

・・・
というか、
　=COUNTA(OFFSET($A$3,,,COUNTA(A:A),1))
これ、スゲー無駄なことをしているように見えるのは自分だけでしょうか。
　＝COUNTA(A:A)-COUNTA(A1:A2)
で良いじゃん。

No.1 回答者： banzaiA 回答日時： 2020/08/29 08:21

Excelのバージョンは？