How can I count items in a filtered list?

[Counting filtered data.]

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

 

[N Harkawat]

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
(e-mail address removed)> wrote in message
news:[email protected]...

 

[tommy]

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

 

[T. Valko]

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"))

 

[tommy]

it works!
thank you

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"))

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Antonella]

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]

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?

 

[Antonella]

Is a data validation drop down list. Can be the reason why does not work?

 

[T. Valko]

Is a data validation drop down list.

Try this:

=SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))

Better to use cells to hold the criteria.

D1 = Netherlands
E1 = July

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))

 

[Antonella]

Thanks.. You made my day!!!
Antonella

Try this:

=SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))

Better to use cells to hold the criteria.

D1 = Netherlands
E1 = July

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Golfer2100]

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]

anyway to do this in a pivot table

See this:

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

 

[Steven j P]

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]

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"))

 

[Steven j P]

T. Valko, Thanks, It did not work at first;

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

Needed to add 1 ")" to make it work, see below, Thanks again

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

Steve

 

[T. Valko]

Needed to add 1 ")" to make it work

Yeah, that was my fault. <argh>

Thanks for the feedback!

 

[Antonella]

Hi I need your help..
I know that is not the right forum but I'll try anyway.
Hope you will understand what I’m trying to do.
I’ve got 2 worksheets called Total.xls Monthly.xls in a workbook called PM.
I’ve linked few cells from Monthly to Total (that’s was easy). Now all the
formula already in Total give Error as results.
Any help greatly appreciated.
Antonella

 

[Gord Dibben]

Your syntax is not correct.

You cannot have Total.xls and Monthly.xls in one workbook.

An *.xls is a single workbook.

Do you mean you have two worksheets named Total and Monthly in a workbook
named PM.xls

Is this latest problem you are posting in any way related to the original
posting about counting filtered items you and Biff have been working on?


Gord Dibben MS Excel MVP