Count multiple numbers in a cell

Hello.

I have a list of 200+ students in 20 courses and, each student has a cell for their attendance for each week. Because of the crazy nature of my school, I can only keep track of the days that students *do* attend. So for the last week in December, a students cell may contain "14" or it may contain "14, 15, 16" indicating that they attended on only the 14th or on 3 days between the 14th and 16th. The cell may also contain text indicating that I have called the student's home or contacted them in some other way.

I need to quickly track how many students have been active in a given week. I have tried many count() type deals, but just can't make it work. I tried to count numbers and changed the commas to decimals, but that didn;t work when I had 14.15.16 -- it isn't a number.

This seems like it should be easy, but I am quite lost. Any help would be greatly appreciated. Manually counting 200+ rows is no fun.

Thanks,
Josh

Josh, I'm not entirely sure I follow... do you mean perhaps something like:

Please Login or Register  to view this content.

though it's not clear if Week1 covers the "same" date range for each row, which I presume represents a given student (?), given the different 1st Day values...

Also given there are external links in the sample it will be hard for people to establish the exact nature of your file.

Thank you for your replies.

The SUMPRODUCT formula has allowed me to calculate at least how many students are "multiple attenders" within a given week. This is very valuable. I would still like to break it down further so that I could say the total times students attended in a week, or to be able to say how many times a given student has attended that month. In that case a cell that contains "30,4,5" would need to be identified as having 3 numbers. Other cells contianing "14,17 C: late work" would need to be broken down to show that there are 2 numbers. (The C: indicates a call home. I place it in the same area as a quick visual to see if I am on top of my students -- format red for no attend that week, format yellow if they didn't show but I did my due dilligence by calling). The calls column is empty -- it usually has a COUNTIF function that searches the weeks for instances of "C:" which would indicate how many times I have called home.

I have attached a 3 person version of my full sheet. It breaks down past AG, so please ignore that. I am sure that my work will appear very pathetic to advanced users, but this sheet does function well as a very quick visual cue for me during hectic school hours.

Thanks,
Josh

PS: I greatly enjoyed looking up and learning what "--" meant in the formula. Fascinating stuff, thanks.

Josh, I think it would help if you could post a file with desired results (calculated manually of course).

As things stand I for one am not sure where you want results to appear nor in fact what it is exactly you wish to calculate ?
Are you looking to calculate the attendances per week per student (and subsequently sum for all) or are you looking to work only in aggregation at week level ?

Might also be worth outlining most convoluted of strings ... eg:
-- are your delimiters consistent for example ie all values are split by comma ?
-- Are the calls always listed post date values or can they appear anywhere within the string ?

A final question would be as to whether or not you're able to implement a VBA based solution - ie a User Defined Function (UDF) ?
If your delimiters are indeed consistent a UDF would IMO (FWIW) be the simplest way to ascertain days attended within any given string(s).

OK. Here is the latest. I added a few more fictional students to make the example marginally more realistic.


RE: your questions
- All attendance days are always split by a comma. Sometimes also a space, but I could always standardize that if need be.
- Calls are always listed after dates, but are first when there are no dates.
- I have never used VBA. I am still learning Excel, but I am not a slow learner. I'd tackle a small bit of VBA if you could help and point me to resources! Maybe then I could create a macro that stamps the non-dynamic current date into a cell. I would be cool to just hit F10 when I enter the "first day" field or the "Unit X issued" date.

Thanks,
Josh

Here's one possible approach:

Please Login or Register  to view this content.

The above, stored in a standard Module within a macro enabled file, can be used like any other function from a cell - using your sample file:

Please Login or Register  to view this content.

the first paramater of the function is the range containing the attendance strings, the second parameter (0/1) stipulates which value is being returned:

0: total no. of students that attended during week
1: total no. of classes attended by all students during that week

obviously by altering the range etc you can limit the calcs to a specific student should you so wish.

From a formula perspective based on sample etc and your prior post I suspect you could use something like the below for total classes attended:

Please Login or Register  to view this content.

Thank you and Happy New year! I really appreciate the time you have given my problem. I tried both solutions (the VBA and the formula) and they work very well, this is a big help. I have two follow-up questions if that is OK:

1. In my 3rd file, I had included a "wish list" calculation of total active students. I may have had a wrong number in there, but this cell would tell me how many students over the whole month had attended at least once. For that sample group the answer would be "4." How do I do this? I tried

Please Login or Register  to view this content.

but ATTENDANCE doesn't like to be 3d, I guess. Forgive me - I don't yet understand the VBA you have given me. I can see that there must be a solution using --ISNUMBER and --LEFT as you have, but I can't think of how to test each row in a 3d array.

2. I noticed your tagline with several readings. Are any of those appropriate for me so that I can learn a bit of VBA, starting by understanding the code you have provided? Or any other reading you think might be appropriate for me? I'd really like to learn more.

Thanks,
Josh

Originally Posted by Humanist

I had included a "wish list" calculation of total active students. I may have had a wrong number in there, but this cell would tell me how many students over the whole month had attended at least once. For that sample group the answer would be "4." How do I do this?

If I've understood, in terms of the UDF:

Please Login or Register  to view this content.

called as before, but you can now use:

Please Login or Register  to view this content.

For a native solution to the above you would in truth be looking at using a relatively expensive Array based solution / Volatile SUMPRODUCT ... if you're happy using the UDF I'd probably just adopt that myself.

Regards M14 (total classes attended in month) you could use a Basic Sum(J14:L14) but it you wish to use UDF for sake of consistency:

Please Login or Register  to view this content.

Re: 2 - the links in my sig. are general suggestions that I found (and still find) useful myself.