I have an excel spread sheet table that I filter. I would like to be able to
count the number of cells in the filtered data. Anyone know how to do this?
CountA returns the number of cells in the unfiltered data.
Tom

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

it works!
thank you

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
it works!
thank you

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

Hi,

Im hoping that someone can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
Ill try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

In both columns data must be chosen
from a drop down menu

Is that a data validation drop down list (or maybe a combo box) or is it an
Auto Filter drop down?


--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Hi,

I'm hoping that someone can help me..
I am trying to count how many export has been done for each Region for
July,
how many for August and so on.
I'll try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands,
Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

biff: NEED SOME OF YOUR EXPERT HELP...

Need to add the number of unique numbers in a column.. for instance i might
have unit number PU114 listed 10 times in a spreadsheet but its only 1
unit..how can i create a formula to do this for me? also, anyway to do this
in a pivot table rather than a traditional count of lines?

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

anyway to do this in a pivot table

See this:

http://contextures.com/xlPivot07.html#Unique

--
Biff
Microsoft Excel MVP


"Golfer2100" wrote in message
...
biff: NEED SOME OF YOUR EXPERT HELP...

Need to add the number of unique numbers in a column.. for instance i
might
have unit number PU114 listed 10 times in a spreadsheet but its only 1
unit..how can i create a formula to do this for me? also, anyway to do
this
in a pivot table rather than a traditional count of lines?

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

This function almost works for except I want to use a wildcard for "A", I
want to use "CL*" cells that begin with "CL". How can I use the * wildcard in
this function.

Steven

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

How can I use the * wildcard in this function
cells that begin with "CL".

You can't use wildcards in this function.

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(LEFT(B2:B100,2="CL"))

--
Biff
Microsoft Excel MVP


"Steven j P" <Steven j wrote in message
...
This function almost works for except I want to use a wildcard for "A", I
want to use "CL*" cells that begin with "CL". How can I use the * wildcard
in
this function.

Steven

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom



The use of the "sumproduct" formula confuses me and how it's applied.

I have a report at work that lists a number of categories for multiple
people. With the use of the "countif" formula I'm able to identify the
quantity associated with each category for the entire group. However, I want
to filter down to a particular individual and have the quantities now only
apply to that individual. Is there a way to combine the "countif" and
"subtotal" formulas to make this happen?

Found this through Excel help - exactly what I wanted, and in less than 5
mins too :-)

Thanks very much

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

You're welcome!

--
Biff
Microsoft Excel MVP


"A Shuttle" <A wrote in message
...
Found this through Excel help - exactly what I wanted, and in less than 5
mins too :-)

Thanks very much

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

It works for me, too! Thanks very much for your help!

Also one quick note for other people trying to count TRUE or FALSE values, I
had to remove the double quotes - apparently Excel see's them as special
values, even though it doesn't seem to convert them to numbers (the way a
database does).
This worked for counting the number of TRUE's in a filtered list:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100=TRUE))

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

trying to count TRUE or FALSE values,
I had to remove the double quotes -
apparently Excel see's them as special values

TRUE and FALSE are special values in Excel. They're called Boolean values.
In Excel a Boolean TRUE and FALSE is different from a *text* entry of true
or false. If no special formatting has been applies a Boolean will appear in
the cell as all uppercase characters and centered in the cell.

--
Biff
Microsoft Excel MVP


"Dan W" <Dan wrote in message
...
It works for me, too! Thanks very much for your help!

Also one quick note for other people trying to count TRUE or FALSE values,
I
had to remove the double quotes - apparently Excel see's them as special
values, even though it doesn't seem to convert them to numbers (the way a
database does).
This worked for counting the number of TRUE's in a filtered list:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100=TRUE))

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

Hi.
I was wondering if you could explain the occurence of TWO adjacent minus
signs part way through this formula.
Thanks,
David

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

--(B2:B100="A")

This expression will return an array of either TRUE or FALSE:

(B2:B100="A")

B2: X
B3: A
B4: A
B5: C

B2="A" = FALSE
B3="A" = TRUE
B4="A" = TRUE
B5="A" = FALSE

SUMPRODUCT calculates numbers so we have to convert those logical TRUE and
FALSE to numbers. The TWO adjacent minus signs, known as double unary, is
one way to do that.

--TRUE = 1
--FALSE = 0

--(B2="A") = 0
--(B3="A") = 1
--(B4="A") = 1
--(B5="A") = 0

So we end up with an array of 1s and 0s:

{0;1;1;0}

The result of the SUBTOTAL function is also an array of 1s and 0s. For
example: {0;1;1;1}.

These 2 arrays are then multiplied together to arrive at the final result of
the formula:

Subtotal......B2:B5="A"
{0;1;1;1}*{0;1;1;0}

0*0 = 0
1*1 = 1
1*1 = 1
1*0 = 0

SUMPRODUCT({0;1;1;0}) = 2

See this for more info:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hi.
I was wondering if you could explain the occurence of TWO adjacent minus
signs part way through this formula.
Thanks,
David

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

This works (great); however, it seem to work only for non-numerical data. I
am analyzing a survey the numerical responses where 5 = strongly agree, 4
=agree, etc. I really don't want to convert it all to alpha-characters since
I am performing other statistical functions on those cells. Any ideas?

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B10 0)-ROW(B2),0,1)),--(B2:B100="A"))
it seem to work only for non-numerical data.

Just a couple of minor changes should do the trick:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100=n))

Where n = the number to count. For example, to count the number of times 5
appears in the filtered (or unfiltered) range:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100=5))

--
Biff
Microsoft Excel MVP


"C Smith" <C wrote in message
...
This works (great); however, it seem to work only for non-numerical data.
I
am analyzing a survey the numerical responses where 5 = strongly agree, 4
=agree, etc. I really don't want to convert it all to alpha-characters
since
I am performing other statistical functions on those cells. Any ideas?

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom

I'm trying to do something similar but when I use this formula i keep getting this, "Error: Argument Must be a range"

On Wednesday, May 13, 2009 at 11:00:41 PM UTC-7, T. Valko wrote:
This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

On Thursday, July 21, 2005 at 7:10:09 PM UTC+2, N Harkawat wrote:
=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be able
to
count the number of cells in the filtered data. Anyone know how to do
this?
CountA returns the number of cells in the unfiltered data.
Tom

This worked for me, thanks! Simple easy solution. You can check in the help file for this function, you add "10" before the function code, in this case "2", thus input "102" to show only "visible" cells. Thus filtered or hidden cells are excluded from the count function.

oand wrote:

On Thursday, July 21, 2005 at 7:10:09 PM UTC+2, N Harkawat wrote:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This worked for me, thanks! Simple easy solution.
[snip]

Hello, Google Groupie. Thanks for replying to a post that is ***THIRTEEN
****ING YEARS OLD***. Yeah, I don't have a better use for my bandwidth,
thanks.

--
I could be wrong but that's never kept me from opening my mouth before.

On Thursday, July 21, 2005 at 1:09:14 PM UTC-4, filtered Counting filtered data. wrote:
I have an excel spread sheet table that I filter. I would like to be able to
count the number of cells in the filtered data. Anyone know how to do this?
This does not Work
I need to count the number for each month (filtered)
Please help

=MONTH(SUBTOTAL(3,AL12:AL10000)=5)

On Friday, July 22, 2005 at 3:09:14 AM UTC+10, filtered Counting filtered data. wrote:
I have an excel spread sheet table that I filter. I would like to be able to
count the number of cells in the filtered data. Anyone know how to do this?
CountA returns the number of cells in the unfiltered data.
Tom

Use the Subtotal function

Function 103 counts only the visible rows in a range

Example :
=subtotal(103,C2:C198)

Helen

On Thursday, July 21, 2005 at 1:09:14 PM UTC-4, filtered Counting filtered data. wrote:
I have an excel spread sheet table that I filter. I would like to be able to
count the number of cells in the filtered data. Anyone know how to do this?
CountA returns the number of cells in the unfiltered data.
Tom

I could not get that to work. I am trying to count the number if names in a column. I would like to filter out some of the names based on gender or other characteristics. How do I do that?

On Sunday, 7 July 2019 21:58:32 UTC+1, wrote:
On Thursday, July 21, 2005 at 1:09:14 PM UTC-4, filtered Counting filtered data. wrote:
I have an excel spread sheet table that I filter. I would like to be able to
count the number of cells in the filtered data. Anyone know how to do this?
CountA returns the number of cells in the unfiltered data.
Tom

I could not get that to work. I am trying to count the number if names in a column. I would like to filter out some of the names based on gender or other characteristics. How do I do that?

=SUBTOTAL(103,your_range)

On Sunday, 7 July 2019 21:58:32 UTC+1, wrote:
On Thursday, July 21, 2005 at 1:09:14 PM UTC-4, filtered Counting filtered data. wrote:
I have an excel spread sheet table that I filter. I would like to be able to
count the number of cells in the filtered data. Anyone know how to do this?
CountA returns the number of cells in the unfiltered data.
Tom

I could not get that to work. I am trying to count the number if names in a column. I would like to filter out some of the names based on gender or other characteristics. How do I do that?

If you use subtotal(103, range:range), then i think that'll do it for you

--


Notice:
This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group.

If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses.

References to the
"Ocado Group" are to Ocado Group plc (registered in England and Wales with
number 7098618) and its subsidiary undertakings (as that expression is
defined in the Companies Act 2006) from time to time. The registered office
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way,
Hatfield, Hertfordshire, AL10 9UL.

To count the number of cells in a filtered list using the SUBTOTAL function:

1. Select the cell where you want to display the count.
2. Type the formula
   Formula:

   "=SUBTOTAL(3,range)" 

   into the cell, replacing "range" with the range of cells you want to count. The "3" in the formula tells Excel to count visible cells only.
3. Press Enter to calculate the count.

That's it! The cell will now display the count of visible cells in the filtered list.