COUNT formula

[KATHWUN]

I want to filter for an item and then count the number of incidents filtered. I know its easy, but. . .it eludes me. TIA

[lars]

subtotal(9,A1:a5)

[KATHWUN]

Lars, you formula looks like a Row 9 A1:A5 subtotal for numbers. I'm trying to COUNT how many times an "Active" status in a Column occurs in my dbase. Something like =COUNT(D:D) -- but this doesn't work. Am I making myself clear? Hope so.

[anno]

don't quite get the 'filter' thing, but how about =COUNTIF(D:D,"Active")?

[Aladin Akyurek]

On 2002-04-11 16:43, KATHWUN wrote:
I want to filter for an item and then count the number of incidents filtered. I know its easy, but. . .it eludes me. TIA

=SUBTOTAL(2,range) [ for COUNT ]

or

=SUBTOTAL(3,range) [ for COUNTA ]

[lars]

Are you filtering it then counting it or just counting it if you just count it it's counta(range)

or if you filter it then use subtotal(3,range) that will count the number of items in the range

if there are other items in the range and you want only certain ones counted then use COUNTIF(range,"name")

Try those

[KATHWUN]

I'll try to make the need clearer:
STATUS Column D has either "Active" or "Inactive". When I filter the column for just "Active" entrys in a large dBase, I want to count how many "Active" cells are filtered out of the whole dBase.
So, I want a formula that will count the number of times the word "Active" occurs in the filtered data. The row numbers aren't consecutive, since they've been filtered out of the dBase. Does this help? I hope so.

[KATHWUN]

My process is to Filter then Count.
Your suggestion below worked:

"if you filter it then use subtotal(3,range) that will count the number of items in the range"

Thanks again, Lars. I'll study the subtotal function further. I don't understand the "3" part.

[Duane]

Anno suggested what I was going to suggest.

=countif(D:D,"active")

Using D:D means it looks for "active" anywhere in D1:D65536; it'll count all instances of the word "active" whether the filter is on or not).

_________________
Regards, Duane

[ This Message was edited by: Duane on 2002-04-11 18:13 ]

[ This Message was edited by: Duane on 2002-04-11 18:16 ]

[Dave Patton]

Several people suggested solutions; what happened when you adjusted to relevant ranges and tried the formula?

HELP has information on Subtotal

Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Syntax

SUBTOTAL(function_num,ref1,ref2,…)

Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.

Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.