数式の数値を除いた数値のカウント

質問

=COUNTIF(A1:A5,">=0")で0以上の数値のセルをカウントしたいのですが、
途中に小計等の「数式の結果の数値」があり、
その数値も含めてカウントされてしまいます。
数式の結果である数値は除いた、直接入力した数値のみカウントすることはできますか？

例えば、A1～A5セルに表示されている数値が

A1：8
A2：-5
A3：空白
A4：3（=SUM(A1:A3)）
A5：2

の場合、=COUNTIF(A1:A5,">=0")で「3」となりますが、
A4の数式の結果の数値の3は含めずに、
数値を直接入力した数で>=0である個数の「2」となるようにカウントするにはどうすればよいでしょう？

やはり「数式の結果の数値」も「直接入力した数値」も
同一のものとしか判断できないのでしょうか？

回答No.4

Wendy02 · ベストアンサー

#2です。
書き忘れていましたが、以下の式は、

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-1,0))*(A1:A5>=0))

A4：3（=SUM(A1:A3)）
ではなく、

ここも、

=SUBTOTAL(9,A1:A3)

を使用します。式の中でカウントされないようにするには、SUBTOTAL関数を使用します。別に、VBAを使うことはやぶさかではありませんが、数式でできることを考えてみました。

補足

おー、これ非常に惜しいです！！！
最初の質問欄に明記しなかったのですが、数式でできるのが第一希望です。

1.　ROW(A1:A5)　の引数が、A3とかA4とか列の途中から始められること
2.　=SUBTOTAL(9,A1:A3)　の関数で合計だけでなく、引き算ができること

この2点が満たせれば解決します。

SUBTOTAL関数、初めて知りましたが非常に便利そうですね。
調べてみたら、カッコ内の最初の数字の集計方法は1～11まであるようですが、
「引き算」はないようですね。
質問に　A4：3（=SUM(A1:A3)）と書きましたが、
=SUM(A1:A3)が=(B2-A1)等の引き算（他列を含む）の場合もあるので、
SUBTOTAL関数で引き算ができれば、と思いました。

うーん、やはり数式ではちょっと無理なのでしょうか。

回答No.5

NNAQ

>　=(B2-A1)等の引き算
=SUBTOTAL(9,B2)-A1
で大丈夫みたいですよ。

#2の式を応用できなければ、空いてる列（仮にC列）に
C1=IF(SUBTOTAL(2,A1),A1,"")
として、
=COUNTIF(C1:C5,">=0")
で、カウントすれば良いと思います。

お礼

これで引き算できました！！
ありがとうございます。

回答No.3

BLUEPIXY

＃１でＣＯＵＮＴＩＦのように条件をつけられるように修正してみました。
使い方：
条件を付けない時
=countConstNum(A1:A5)
条件を付ける時
=countConstNum(A1:A5,">=0")
----------------------------------------------------------------
Public Function countConstNum(r As Range, Optional cond As String = "")
Dim c
Dim x As Range
c = 0
For Each x In r
If IsNumeric(x.Formula) Then
If cond = "" Then
c = c + 1
ElseIf Application.Evaluate(x.Value & cond) Then
c = c + 1
End If
End If
Next
countConstNum = c
End Function

回答No.2

Wendy02

こんばんは。

このようにすればよいと思います。

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-1,0))*(A1:A5>=0))

ただし、ROW(A1:A5)　の引数は、どこでも、A1から始まります。横の場合は、COLUMN(A1:E1)と数えます。

回答No.1

BLUEPIXY

件数を求めるだけだったら、
調べる範囲を選択してから
メニューの編集～ジャンプ～セル選択
定数で数値
を選んで実行すると、定数として入力されているセルが選択されます。
この時、ステータスバーで「データの個数」を選べば件数がわかります。
式としてカウントしたい場合は
ユーザー定義関数として
Public Function countConstNum(r As Range)
Dim c
Dim x As Range
c = 0
For Each x In r
If IsNumeric(x.Formula) Then c = c + 1
Next
countConstNum = c
End Function
のようなのを標準モジュールに作成します。

お礼

早速の回答ありがとうございます。
質問欄に明記しなかったのですが、数式でできるのが第一希望ですので
もうちょっと数式しらべてみます。

実はユーザー定義関数や標準モジュール等やったことがなく、
できれば数式で、という状態です。
しかし、いつかはマクロやVBAもできるようにならなければと思っていたので、
BLUEPIXYさんのアドバイスを参考に、勉強してみます。
No5でのアドバイスも参考にします。

ありがとうございました。