PDA

View Full Version : [SOLVED] Counting Names in List that have completed any one of a Number of Courses



vanhunk
07-01-2014, 01:24 AM
Counting the people that have completed any one of a number of courses:
(Formula Result Required; Excel 2010)

I have a list of names in a column (each name in its own row) and a number of column headings in a row (let's say 4). The columns headings represent courses conducted. Say "Course 1", "Course 2", "Course 3", and "Course 4". If a person has attended one of these courses the duration of the course will be typed into the corresponding intersection of name and course.

I need to count the number of people from the list that has attend a (i.e. any) of these courses. For example, if Person1 attended one or more of the courses, it will represent a 1. If he/she didn't attend any of the courses it will represent a 0.

Thus if there are 6 people and 4 of them attended one or more of the courses, the number I am looking for is 4.

Thank you very much,
Best regards,
vanhunk

ashleyuk1984
07-01-2014, 04:27 AM
I'm sure this can be done pretty easily. However, it would be useful to see how your data is outlined.
Could you please upload your workbook, doesn't need to be the complete workbook if you have confidential information.
Just enough so that we can see how everything is labelled.

vanhunk
07-01-2014, 04:50 AM
@ashleyuk1984:
I have attached an example. There are probably many ways of doing it, but I would love something with an OR in it.

Regards,
vanhunk

ashleyuk1984
07-01-2014, 04:59 AM
I'm having trouble understanding your "Count" on the provided example??

http://ultraimg.com/images/hkcLF.png

Could you please explain how you come to 8 in section A ??

vanhunk
07-01-2014, 05:13 AM
@ashleyuk1984:
My apologies, I have attached a new spreadsheet. The previous figures where clearly not correct.

Regards,
vanhunk

ashleyuk1984
07-01-2014, 05:40 AM
Thanks.
I propose that you do something like this. Now that you have the formulas, you can rearrange the look of the spreadsheet how you please.
For instance, you can hide the count column as such, or move it to another location if you wanted.

I hope this helps.

vanhunk
07-01-2014, 06:10 AM
@ashleyuk1984:
Thanks Ashley,
I would like to do it without a helper column.

Is there no way it can be done with one of the count or countif formulas, with or without an array formula?

There must be a way, but I just can not get the syntax right.

Something that would somehow look like "{=COUNTIF(D413,OR(E4:E13>0,F4:F13>0,G4:G13>0))}" ???

Regards,
vanhunk

lecxe
07-01-2014, 05:41 PM
Hi vanhunk

For ex. for the first group try:

=COUNT(1/FREQUENCY(ROW(E4:G13)*(E4:G13<>""),ROW(E4:G13)))

vanhunk
07-02-2014, 04:56 AM
Thanks lecxe,
This is exactly the type of formula I was looking for.

vanhunk

lecxe
07-02-2014, 07:15 AM
You're welcome. Thanks for the feedback.