Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old July 18th 09, 11:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 8
Default How can I count items in a filtered list?

Thanks.. You made my day!!!
Antonella

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Is a data validation drop down list. Can be the reason why does not work?

"T. Valko" wrote:

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
news 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













  #12   Report Post  
Old July 19th 09, 02:16 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default How can I count items in a filtered list?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Thanks.. You made my day!!!
Antonella

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Is a data validation drop down list. Can be the reason why does not
work?

"T. Valko" wrote:

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
news 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














  #13   Report Post  
Old July 21st 09, 08:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 2
Default How can I count items in a filtered list?

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
news
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






  #14   Report Post  
Old July 22nd 09, 02:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default How can I count items in a filtered list?

anyway to do this in a pivot table

See this:

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

--
Biff
Microsoft Excel MVP


"Golfer2100" wrote in message
news
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
news
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








  #15   Report Post  
Old August 3rd 09, 03:09 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 1
Default How can I count items in a filtered list?

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
news
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








  #17   Report Post  
Old August 3rd 09, 06:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 1
Default How can I count items in a filtered list?

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" wrote:

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
news 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









  #18   Report Post  
Old August 3rd 09, 06:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default How can I count items in a filtered list?

Needed to add 1 ")" to make it work

Yeah, that was my fault. <argh

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steven j P" wrote in message
...
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" wrote:

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
news 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











  #19   Report Post  
Old August 6th 09, 06:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 8
Default How can I count items in a filtered list?

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



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Thanks.. You made my day!!!
Antonella

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Is a data validation drop down list. Can be the reason why does not
work?

"T. Valko" wrote:

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
news 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















  #20   Report Post  
Old August 6th 09, 07:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default How can I count items in a filtered list?

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

On Thu, 6 Aug 2009 10:10:15 -0700, Antonella
wrote:

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



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Thanks.. You made my day!!!
Antonella

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Antonella" wrote in message
...
Is a data validation drop down list. Can be the reason why does not
work?

"T. Valko" wrote:

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
news 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


















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 0 May 15th 05 08:14 PM
Items in a List jcliquidtension Excel Discussion (Misc queries) 1 April 5th 05 10:23 PM
How do I find out what items are in one list but not in another l. Michelle Craig Excel Discussion (Misc queries) 2 December 22nd 04 08:32 PM


All times are GMT +1. The time now is 11:38 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017