formula to count sets of repeating numbers

[Excel-User-RR]

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

 

[Glenn]

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

 

[Excel-User-RR]

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.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(FREQUENCY(A2:A9,A2:A9)=N))

Where N = sessions attended by N people

 

[Ashish Mathur]

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]

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]

You're welcome. Thanks for the feedback!