Count the number between range of cell for a particular person

[faizzsheikh]

I want to count the numbers between 1 to 10 from the range for
particular person as seen in the below example.

Keyword Rank__________Person Name
1_____________John
5_____________Agnes
8_____________Agnes
12____________John
20____________Agnes
13____________Agnes
3_____________John
40____________John
8_____________John
15____________Agnes
10____________John


I want to count the the numbers that John has between 1 to 10 only. I
the above case John must have 4 Keyword Ran between 1 to 10

Please help!


Thanks in advance
Fai

 

[Spencer101]

I want to count the numbers between 1 to 10 from the range for
particular person as seen in the below example

Keyword Rank__________Person Nam
1_____________Joh
5_____________Agne
8_____________Agne
12____________Joh
20____________Agne
13____________Agne
3_____________Joh
40____________Joh
8_____________Joh
15____________Agne
10____________Joh


I want to count the the numbers that John has between 1 to 10 only. I
the above case John must have 4 Keyword Ran between 1 to 1

Please help


Thanks in advanc
Fai

Hi Faiz

Assuming your example data covers cells A1:B12 with the headers in ro
1, you can use
*=SUMPRODUCT((B2:B12="John")*(A2:A12>=1)*(A2:A12<=10))* in any versio
of Excel o
*=COUNTIFS(B2:B12,"John",A2:A12,">=1",A2:A12,"<=10")* in Excel 2007 o
later

 

[Kevin@Radstock]

Hi faizzsheikh

So you want to count how many time John has a value between 1-10,try on
of the following and assuming the data is in A1:B11

For Excel 2007>: =COUNTIFS(B1:B11,"John",A1:A11,">=1",A1:A11,"<=10")

For Excel 2003<: =SUMPRODUCT((B1:B11="John")*(A1:A11>=1)*(A1:A11<=10))

Kevin

 

[Claus Busch]

Hi,

Am Mon, 13 May 2013 08:07:41 +0100 schrieb faizzsheikh:

I want to count the numbers between 1 to 10 from the range for a
particular person as seen in the below example.

Keyword Rank__________Person Name
1_____________John
5_____________Agnes
8_____________Agnes
12____________John
20____________Agnes
13____________Agnes
3_____________John
40____________John
8_____________John
15____________Agnes
10____________John

try:
=COUNTIFS(C2:C12,"John",B2:B12,"<=10")
for xl2007 or later
else try:
=SUMPRODUCT(--(C2:C12="John"),--(B2:B12<=10))


Regards
Claus Busch