View Single Post
  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic[_2_] Domenic[_2_] is offline
external usenet poster
 
Posts: 265
Default How can I count items in a filtered list?

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 ,
Dominic_gates wrote:

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

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