Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count the number between range of cell for a particular person
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 I want to count the the numbers that John has between 1 to 10 only. In the above case John must have 4 Keyword Ran between 1 to 10 Please help! Thanks in advance Faiz |
#2
|
|||
|
|||
Quote:
Hi Faiz, Assuming your example data covers cells A1:B12 with the headers in row 1, you can use =SUMPRODUCT((B2:B12="John")*(A2:A12=1)*(A2:A12<=1 0)) in any version of Excel or =COUNTIFS(B2:B12,"John",A2:A12,"=1",A2:A12,"<=10" ) in Excel 2007 or later. |
#3
|
|||
|
|||
Hi faizzsheikh
So you want to count how many time John has a value between 1-10,try one 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<=1 0)) Or a Pivot table Kevin Quote:
Last edited by Kevin@Radstock : May 13th 13 at 09:52 AM |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the number between range of cell for a particular person
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the number of a range of words in a cell | Excel Worksheet Functions | |||
Count the number of times a cell value is within a specific range | Excel Worksheet Functions | |||
count a number range and a letter in a cell | Excel Worksheet Functions | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions |