setting two conditions for countif

[Danny J]

Hi folks,

I am stuck and any help would be appreciated. I want to set a condition
which counts the number of people who have come on an outlook course. I
created the following equation:- =countif(A1:E20,and(b2="outlook",c2="y")).
A1:A20 being the worksheet, column A being the delegate name, column B the
course name and column c being did the delegate attend, y/n?

Looking at it I realise it must be wrong but cannot think of what the answer
should be.

Help!

Thanks,

Danny

 

[JE McGimpsey]

If I understand you correctly, here's one way:

=SUMPRODUCT(--(B1:B20="outlook"),--(C1:C20="y"))


For an explanation of the "--", take a look here:

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Frank Kabel]

Hi
use sUMPRODUCT:
=SUMPRODUCT(--(B1:B100="outlook"),--(C1:C100="y"))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A1:A20="name"),--(B1:B20="outlook"),--(C1:C20="y"))

if you want to count all names in A1:A20 when b is outlook and c is y use

=SUMPRODUCT(--(A1:A20<>""),--(B1:B20="outlook"),--(C1:C20="y"))


Regards,

Peo Sjoblom

 

[Danny J]

Wow! Speedy reply! Thanks Frank and a very interesting web link too!

Danny

 

[Danny J]

Thanks Peo,

I have some questions concerning Sum product which I have sent to the
newsgroup...hopefully it will appear soon.

Best wishes,

Danny