Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #31   Report Post  
Old December 8th 09, 09:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,560
Default How can I count items in a filtered list?

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







  #32   Report Post  
Old December 8th 09, 05:39 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?

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








  #33   Report Post  
Old January 30th 10, 09:44 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1
Default How can I count items in a filtered list?

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






  #34   Report Post  
Old January 30th 10, 10:42 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?

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








  #35   Report Post  
Old February 4th 10, 04:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2010
Posts: 3
Default How can I count items in a filtered list?

Hi just a noob here,

I would like to know how or what function do I need to use on this kind of
problem. I am creating a template where the 2nd column B3:B50 has a drop down
option, so I'd like that Column to be counted examples on the drop down are .
NET - TNBA Callbacks and NRA - Montreal EMT.

Ex. if B2:B50 have (10) . NET - TNBA Callbacks it will appear (10) on J15
and if NRA - Montreal EMT have (3) it must appear (3) on J16.

hope that helps in explaining...its too hard to explain, I am wishing if I
could attach the file for better view..

thanks again.. waiting for your response





  #36   Report Post  
Old February 4th 10, 04:51 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?

the 2nd column B3:B50 has a drop down option

Assuming that means you have AutoFilter applied...

Try these:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B50,ROW(B3:B50)-ROW(B3),0,1)),--(B3:B50="NET
- TNBA Callbacks"))

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B50,ROW(B3:B50)-ROW(B3),0,1)),--(B3:B50="NRA
- Montreal EMT"))

--
Biff
Microsoft Excel MVP


"kosageinusha18" wrote in message
...
Hi just a noob here,

I would like to know how or what function do I need to use on this kind of
problem. I am creating a template where the 2nd column B3:B50 has a drop
down
option, so I'd like that Column to be counted examples on the drop down
are .
NET - TNBA Callbacks and NRA - Montreal EMT.

Ex. if B2:B50 have (10) . NET - TNBA Callbacks it will appear (10) on J15
and if NRA - Montreal EMT have (3) it must appear (3) on J16.

hope that helps in explaining...its too hard to explain, I am wishing if I
could attach the file for better view..

thanks again.. waiting for your response





  #37   Report Post  
Old July 7th 16, 05:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2016
Posts: 1
Default How can I count items in a filtered list?

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




  #38   Report Post  
Old May 17th 17, 08:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2017
Posts: 5
Default How can I count items in a filtered list?

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)
  #39   Report Post  
Old September 24th 17, 12:58 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 1
Default How can I count items in a filtered list?

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
  #40   Report Post  
Old January 11th 18, 08:59 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 1
Default How can I count items in a filtered list?

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.


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 08:03 AM.

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