COUNTIF multi criteria

[david_g]

I am not sure whether the COUNTIF function is the best solution to the
problem of counting for two or more criteria. I want a spreadsheet
solution to allow students to select their course elements. For the
diploma award they need 5 compulsory from a choice of 10 AND 10
optional elements from a choice of 90. For a certificate award they
need 3 compulsory and 6 optional elements from the same list.
I envisage a1 to a100 as description of the elements, b1 to b100 as
either "compulsory" or "optional". The student will tick in column C
(I don't quite know how to do a "tick" but any character will do...and
ideas?) . It would be nice to have a prompt saying that "you need X
compulsory and Y optional elements", any ideas. The form can be
accepted when the student has ticked the required number of compulsory
and optional elements.

 

[Bob Phillips]

David,

Format column C as Marlett, ,and use the letter a for the tick, and then
this formula

=IF(AND(SUMPRODUCT(--(B1:B100="Optional"),--(C1:C100="a"))>9,SUMPRODUCT(--(B
1:B100="Compulsory"),--(C1:C100="a"))>4),"Diploma",IF(AND(SUMPRODUCT(--(B1:B
100="Optional"),--(C1:C100="a"))>5,SUMPRODUCT(--(B1:B100="Compulsory"),--(C1
:C100="a"))>2),"Certificate","Nothing"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Zack Barresse]

This may help a little, I like it best for it's links at the end ...

http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=42

HTH