COUNTIF multi criteria

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.

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)


"david_g" <david@gts.co.uk> wrote in message
news:1131644651.998155.51360@g47g2000cwa.googlegroups.com...
> 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.
>

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

http://www.vbaexpress.com/forum/arti...ticle&artid=42

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.


"david_g" <david@gts.co.uk> wrote in message
news:1131644651.998155.51360@g47g2000cwa.googlegroups.com...
>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.
>