Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count sets of repeating numbers
Please lend expertise to the following dilemma: In the list below column A
contains repeating session numbers. Column B contains the session date. Column C contains the person's name. How can I count the number of sessions with people attending in specific amounts? For example I would like to know how many sessions were attended by 2 people, by 3 people, by 4 people, etc. Each result would be in a separate cell. Thanks for your time and assistance with this Excel 2003 formula. Per the example the results should be: 2 people=1 session (#2), 3 people=2 sessions (#s 1 & 3). A B C 1 2/13/09 Mike Jones 1 2/13/09 Sue Day 1 2/13/09 Joe Blow 2 2/20/09 Wes East 2 2/20/09 Lars Lyes 3 2/22/09 Val Zunn 3 3/2/09 Xara Qu 3 3/2/09 Kyle Miles |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count sets of repeating numbers
Excel-User-RR wrote:
Please lend expertise to the following dilemma: In the list below column A contains repeating session numbers. Column B contains the session date. Column C contains the person's name. How can I count the number of sessions with people attending in specific amounts? For example I would like to know how many sessions were attended by 2 people, by 3 people, by 4 people, etc. Each result would be in a separate cell. Thanks for your time and assistance with this Excel 2003 formula. Per the example the results should be: 2 people=1 session (#2), 3 people=2 sessions (#s 1 & 3). A B C 1 2/13/09 Mike Jones 1 2/13/09 Sue Day 1 2/13/09 Joe Blow 2 2/20/09 Wes East 2 2/20/09 Lars Lyes 3 2/22/09 Val Zunn 3 3/2/09 Xara Qu 3 3/2/09 Kyle Miles One way would be to add the following in D2:D9 (assuming the data above is in A2:C9 with headers in row 1): =IF(COUNTIF($A$2:A2,A2)=1,COUNTIF($A$2:$A$9,A2),"" ) Put "Attendees" in D1. Then you could create a PivotTable with "Attendees" in the row field (un-check blanks) and Count of "Session Number" in data items. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count sets of repeating numbers
I appreciate for your suggestion, however I would like to keep the results on
the same worksheet using formulas instead of pivot tables. I will continue to try to find a solution. Thanks. "Glenn" wrote: Excel-User-RR wrote: Please lend expertise to the following dilemma: In the list below column A contains repeating session numbers. Column B contains the session date. Column C contains the person's name. How can I count the number of sessions with people attending in specific amounts? For example I would like to know how many sessions were attended by 2 people, by 3 people, by 4 people, etc. Each result would be in a separate cell. Thanks for your time and assistance with this Excel 2003 formula. Per the example the results should be: 2 people=1 session (#2), 3 people=2 sessions (#s 1 & 3). A B C 1 2/13/09 Mike Jones 1 2/13/09 Sue Day 1 2/13/09 Joe Blow 2 2/20/09 Wes East 2 2/20/09 Lars Lyes 3 2/22/09 Val Zunn 3 3/2/09 Xara Qu 3 3/2/09 Kyle Miles One way would be to add the following in D2:D9 (assuming the data above is in A2:C9 with headers in row 1): =IF(COUNTIF($A$2:A2,A2)=1,COUNTIF($A$2:$A$9,A2),"" ) Put "Attendees" in D1. Then you could create a PivotTable with "Attendees" in the row field (un-check blanks) and Count of "Session Number" in data items. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count sets of repeating numbers
Try this:
=SUMPRODUCT(--(FREQUENCY(A2:A9,A2:A9)=N)) Where N = sessions attended by N people -- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Please lend expertise to the following dilemma: In the list below column A contains repeating session numbers. Column B contains the session date. Column C contains the person's name. How can I count the number of sessions with people attending in specific amounts? For example I would like to know how many sessions were attended by 2 people, by 3 people, by 4 people, etc. Each result would be in a separate cell. Thanks for your time and assistance with this Excel 2003 formula. Per the example the results should be: 2 people=1 session (#2), 3 people=2 sessions (#s 1 & 3). A B C 1 2/13/09 Mike Jones 1 2/13/09 Sue Day 1 2/13/09 Joe Blow 2 2/20/09 Wes East 2 2/20/09 Lars Lyes 3 2/22/09 Val Zunn 3 3/2/09 Xara Qu 3 3/2/09 Kyle Miles |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count sets of repeating numbers
Hi,
You can use the COUNTIF() function as well. 1. Suppose your data is arranged as follows in range C19:E27 Session Date Name Occurrence 1 2/13/09 Mike Jones 1 1 2/13/09 Sue Day 2 1 2/13/09 Joe Blow 3 2 2/20/09 Wes East 1 2 2/20/09 Lars Lyes 2 3 2/22/09 Val Zunn 1 3 03-02-2009 Xara Qu 2 3 03-02-2009 Kyle Miles 3 2. Enter Occurrence in cell F19; 3. In cell F20, enter COUNTIF($C$20:$C20,C20) 4. In cell D30 and D31, enter 2 and 3; 5. In F30, enter =COUNTIF($F$20:$F$27,D30) and copy down -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Excel-User-RR" wrote in message ... Please lend expertise to the following dilemma: In the list below column A contains repeating session numbers. Column B contains the session date. Column C contains the person's name. How can I count the number of sessions with people attending in specific amounts? For example I would like to know how many sessions were attended by 2 people, by 3 people, by 4 people, etc. Each result would be in a separate cell. Thanks for your time and assistance with this Excel 2003 formula. Per the example the results should be: 2 people=1 session (#2), 3 people=2 sessions (#s 1 & 3). A B C 1 2/13/09 Mike Jones 1 2/13/09 Sue Day 1 2/13/09 Joe Blow 2 2/20/09 Wes East 2 2/20/09 Lars Lyes 3 2/22/09 Val Zunn 3 3/2/09 Xara Qu 3 3/2/09 Kyle Miles |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count sets of repeating numbers
Good Morning,
Thank you very much for your help. It did the trick. I appreciate all responses from everyone who posted. This discussion group has helped me many times. Thanks Again! "T. Valko" wrote: Try this: =SUMPRODUCT(--(FREQUENCY(A2:A9,A2:A9)=N)) Where N = sessions attended by N people -- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Please lend expertise to the following dilemma: In the list below column A contains repeating session numbers. Column B contains the session date. Column C contains the person's name. How can I count the number of sessions with people attending in specific amounts? For example I would like to know how many sessions were attended by 2 people, by 3 people, by 4 people, etc. Each result would be in a separate cell. Thanks for your time and assistance with this Excel 2003 formula. Per the example the results should be: 2 people=1 session (#2), 3 people=2 sessions (#s 1 & 3). A B C 1 2/13/09 Mike Jones 1 2/13/09 Sue Day 1 2/13/09 Joe Blow 2 2/20/09 Wes East 2 2/20/09 Lars Lyes 3 2/22/09 Val Zunn 3 3/2/09 Xara Qu 3 3/2/09 Kyle Miles |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count sets of repeating numbers
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Good Morning, Thank you very much for your help. It did the trick. I appreciate all responses from everyone who posted. This discussion group has helped me many times. Thanks Again! "T. Valko" wrote: Try this: =SUMPRODUCT(--(FREQUENCY(A2:A9,A2:A9)=N)) Where N = sessions attended by N people -- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Please lend expertise to the following dilemma: In the list below column A contains repeating session numbers. Column B contains the session date. Column C contains the person's name. How can I count the number of sessions with people attending in specific amounts? For example I would like to know how many sessions were attended by 2 people, by 3 people, by 4 people, etc. Each result would be in a separate cell. Thanks for your time and assistance with this Excel 2003 formula. Per the example the results should be: 2 people=1 session (#2), 3 people=2 sessions (#s 1 & 3). A B C 1 2/13/09 Mike Jones 1 2/13/09 Sue Day 1 2/13/09 Joe Blow 2 2/20/09 Wes East 2 2/20/09 Lars Lyes 3 2/22/09 Val Zunn 3 3/2/09 Xara Qu 3 3/2/09 Kyle Miles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count sets of cells in two columns? | Excel Worksheet Functions | |||
I want random numbers generated without repeating numbers | Excel Worksheet Functions | |||
How to count sets of numbers | Charts and Charting in Excel | |||
How do I check repeating sets of number in a range | Excel Worksheet Functions | |||
Need to display 4 sets of data on one x-axis with 4 repeating y a. | Charts and Charting in Excel |