# Thread: yet another multiple condition challenge (97 - 2002)

1. ## yet another multiple condition challenge (97 - 2002)

Yet again I am faced with multiple conditions when trying to count. The situation is that in a school, students are scheduled for tests of various days. Some students take one test, others take multiple. A student only needs ONE answer sheet per day regardless the number of tests taken that day. I am trying to develop a formula that will compare testing periods to determine if a student is testing so I can count the number of answer sheets required. In the attached example (a very small example), it should be clear that on 5/27/03 there are nine answer sheets needed by the teacher (Bigham) since there are nine individual students testing that day although they are taking more than nine tests. On 5/28/03, there are only six answer sheets required.

I need to count the number of answer sheets required, per day, by teacher. The actual spreadsheet has as many approx 3000 students and 150 teachers.

I thought some variation of an array formula that counts students with at least one non blank cell during a test date per teacher. However, my brain hurts after thinking about it all day. Help!

2. ## Re: yet another multiple condition challenge (97 - 2002)

Jan Karel or Steve will probably come up with something better, but here is my attempt. It needs an intermediate column for each day (which may be hidden, however). It contains formulas like
=(COUNTA(B33)>0)
in cell I3 to determine if there is at least one test on a day for a student. I use an array formula to get the count for a given teacher:
=SUM((\$H\$3:\$H\$22=\$L3)*I\$3:I\$22)
(the name of the teacher is in cell L3.) See attached workbook.

3. ## Re: yet another multiple condition challenge (97 - 2002)

<P ID="edit" class=small>(Edited by sdckapr on 26-May-03 22:21. Edited to put in improved, simpler formula)</P>Put the dates in K1, L1, M1 etc
Put the teacher names in J2, J3, J4, J5, etc

Try this ARRAY formula(confirm with ctrl-shift-enter) in K2 (one line)
=SUM(IF(MMULT((IF((NOT((ISBLANK(\$B\$3:\$G\$12))))*(\$H \$3:\$H\$12=\$J2),1,0)), TRANSPOSE(IF((\$B\$1:\$G\$1=K\$1),1,0)))>0,1,0))

Copy this into as many columns and rows as appropriate.
Change the range lengths as appropriate (A3:a12) is student list,
B1:G1 = dates, B3:g12 is whole range of data, H3:H12 is teacher list

Use range names if appropriate.

I will leave it as a "learning exercise" to figure out how and why it works!

Steve

4. ## Re: yet another multiple condition challenge (97 - 2002)

Thanks - it does work, of course. I don't understand why or how. I could not help but notice that your post was edited to put in an improved, simpler formula. All I can say is, this is absolutely not simple. Maybe improved but not simple. Thanks again.

5. ## Re: yet another multiple condition challenge (97 - 2002)

Neat. Good thinking.

And no, I'm not going to try and simplify <g>.

6. ## Re: yet another multiple condition challenge (97 - 2002)

Steve didn't state that the formula was simple, only that is was simpler than the one it replaces <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

7. ## Re: yet another multiple condition challenge (97 - 2002)

Right you are, Hans (simpler, not simple). I don't think ANY of the arrays formulas are "simple" and this one comes with the added "bonus" of having some "Matrix-math" (I could say it was in honor of the new movie, but it seemed to be the "simplest" way to accomplish what Don requested.)

If anyone caught my original it had a few extra portions, that I realized after posting, they could be combined differently and some of them removed. Some just come about when I build/test them and then forget to remove after it works.

Steve

8. ## Re: yet another multiple condition challenge (97 - 2002)

Here is yet another "simpler" formula. I got rid of 3 "unneeded" IFs.
=SUM((MMULT(((NOT((ISBLANK(\$B\$3:\$G\$12))))*(\$H\$3:\$H \$12=\$J2)), TRANSPOSE(\$B\$1:\$G\$1=K\$1)*1)>0)*1)

I don't think I can get it too much simpler, with the given constraints of the problem, but I am open to suggestions.

Steve

9. ## Re: yet another multiple condition challenge (97 - 2002)

Hey Don,

I am in awe of the elegant and mathematical formulas that complex minds concoct. But I'm a simple country girl. Some would say simple-minded. So this is how I'd solve your problem: a couple columns like the ones suggested before to get daily counts and then a Pivot Table to do all the work of adding it up by teacher for me. Simple AND lazy!

See attachment

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•