How can I count items in a filtered list?

[filtered data.@discussions.microsoft.com 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
data.@discussions.microsoft.com> wrote in message
news:E31F9B08-CDC1-4BF4...@microsoft.com...

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

--
Biff
Microsoft Excel MVP

"tommy" <to...@discussions.microsoft.com> wrote in message
news:D75D8700-C5D1-4ACD...@microsoft.com...

[tommy]

it works!
thank you

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"tommy" <to...@discussions.microsoft.com> wrote in message

news:3689E1F1-14CA-4D51...@microsoft.com...

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

--
Biff
Microsoft Excel MVP

"Antonella" <Anto...@discussions.microsoft.com> wrote in message
news:E524FB16-EC23-40E2...@microsoft.com...

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

--
Biff
Microsoft Excel MVP

"Antonella" <Anto...@discussions.microsoft.com> wrote in message

news:C4A6024B-5EFC-4099...@microsoft.com...

[Antonella]

Thanks.. You made my day!!!
Antonella

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Antonella" <Anto...@discussions.microsoft.com> wrote in message

news:CAF11EE1-94F3-46DA...@microsoft.com...

[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

--
Biff
Microsoft Excel MVP

"Golfer2100" <Golfe...@discussions.microsoft.com> wrote in message
news:D4131D0E-FD84-42D3...@microsoft.com...

[j P@discussions.microsoft.com 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"))

--
Biff
Microsoft Excel MVP

"Steven j P" <Steven j P...@discussions.microsoft.com> wrote in message
news:6B857EC3-65B9-479A...@microsoft.com...

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

--
Biff
Microsoft Excel MVP

"Steven j P" <Stev...@discussions.microsoft.com> wrote in message
news:1A7FD00A-4D15-4478...@microsoft.com...

[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

[Antonella]

Yep I've got 2 worksheets linked to each other. How can I make a formula
works if the cells I need to calculate are linked to the first worksheet.
Hope do you get my point?

[Gord Dibben]

Again I ask............are your calculations concerning what you and Biff
were discussing?

I have not follwed the thread so not sure what you're asking for.

But you can calculate using linked cells.

Say sheet1 has these linked cells in A1:A3

=Sheet2!G1
=Sheet2!H2
=Sheet2!D1

Enter =SUM(A1:A3) in any cell on sheet1

If not clear to you, post some examples of linked cells and what type of
calculating you need done.

Gord

[Dominic_gates]

Hi,

I wonder if someone can help me,

I am trying to count the number of individuals in two seperate columns on
the same spreadsheet based on values that are text and numbers, i.e. "in
column B1:B391 which are 'Con'" and "in column K1:K391 which have a value of
'1'".

i have tried the formula suggested below:

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

adapting this to:

=SUMPRODUCT(--(B1:B391="Con"),--(K1:K391="1"))

however this does not work.

Any help would be amazing

Many Thanks

Dominic

[Domenic]

Try removing the quotes from the second criteria...

=SUMPRODUCT(--(B1:B391="Con"),--(K1:K391=1))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article <9B72BF71-A97D-4B8F...@microsoft.com>,

[Dominic_gates]

Amazing! it works thank you!!!

Many Thanks

Dominic

[Skowron@discussions.microsoft.com R. Skowron]

"T. Valko" wrote:

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

[Shuttle@discussions.microsoft.com A Shuttle]

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

Thanks very much

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

"A Shuttle" <A Shu...@discussions.microsoft.com> wrote in message
news:AFA17DC1-63C0-4D16...@microsoft.com...

[W@discussions.microsoft.com Dan W]

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]

>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 W...@discussions.microsoft.com> wrote in message
news:A1B1C5E2-178C-44E2...@microsoft.com...

[David]

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

[T. Valko]

--(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" <Da...@discussions.microsoft.com> wrote in message
news:9315649E-E731-4380...@microsoft.com...

[Smith@discussions.microsoft.com C Smith]

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]

>>=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-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 Sm...@discussions.microsoft.com> wrote in message
news:C8D5A4F3-D080-499B...@microsoft.com...

[kosageinusha18]

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

[T. Valko]

>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" <kosagei...@discussions.microsoft.com> wrote in message
news:4767731A-8C3D-42EF...@microsoft.com...

[cine...@gmail.com]

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

[sabreind...@gmail.com]

On Thursday, July 21, 2005 at 1:09:14 PM UTC-4, filtered data.@discussions.microsoft.com 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)

[helen...@gmail.com]

On Friday, July 22, 2005 at 3:09:14 AM UTC+10, filtered data.@discussions.microsoft.com 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

[oa...@bluenova.co.za]

On Thursday, July 21, 2005 at 7:10:09 PM UTC+2, N Harkawat wrote:
> =subtotal(2,a1:a1000)
>
> "Counting filtered data." <Counting filtered
> data.@discussions.microsoft.com> wrote in message
> news:E31F9B08-CDC1-4BF4...@microsoft.com...

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

[Auric__]

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

[rwil...@nafcs.org]

On Thursday, July 21, 2005 at 1:09:14 PM UTC-4, filtered data.@discussions.microsoft.com 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?

[Roger Govier]

=SUBTOTAL(103,your_range)

[davy....@ocado.com]

On Sunday, 7 July 2019 21:58:32 UTC+1, rwil...@nafcs.org wrote:

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.