How can I count items in a filtered list?

G

Guest

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

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

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

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

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

tommy

it works!
thank you

T. Valko said:
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"))
Click to expand...
 
T

tommy

it works!
thank you

T. Valko said:
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"))
Click to expand...
 
A

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

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

Antonella

Thanks.. You made my day!!!
Antonella

T. Valko said:
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))
Click to expand...
 
G

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?
 
S

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

T. Valko

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtered table using Countif 6
How do I 'count' the number of entrances in filtered cells? 2
Count Advanced Filtered Rows 3
Counting Filtered Results Using Autofilter 2
Count Function in a Filtered List 1
Sum after Filter, Count after Fliter 2
count unique values with auto-filter on 6
Count Position of Filtered TEXT cells in a column 8

Top