COUNT formula

KATHWUN

Board Regular
Joined
Apr 7, 2002
Messages
61
I want to filter for an item and then count the number of incidents filtered. I know its easy, but. . .it eludes me. TIA
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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 ]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top