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

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

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


For an explanation of the "--", take a look he

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


In article ,
"Danny J" wrote:

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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Danny J" schrieb im Newsbeitrag
...
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

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" wrote:

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

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

Danny


"Frank Kabel" wrote in message
...
Hi
use sUMPRODUCT:
=SUMPRODUCT(--(B1:B100="outlook"),--(C1:C100="y"))

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Danny J" schrieb im Newsbeitrag
...
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

Thanks Peo,

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

Best wishes,

Danny

"Peo Sjoblom" wrote in message
...
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" wrote:

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