If Question

Hi everyone

Hope this isn't a silly question (as think I may have made it harder in my
head than it needs to be! given all the formulas I'm putting in are bringing
back errors!!)

I have one worksheet which gathers info from other sheets and has the
following info

A B C D
Name % % %

In another worksheet I have a list of names (column M say M3) next to the
names I want to put in a formula that will give me the following results:

If M3 is in worksheet 1 and the corresponding cells to that name in b, c and
d are all 0% write Yes otherwise write No but if M3 doesnt match anything on
the list write - .

Thanks as always

Kiwi

Try this...

Assuming this data:

A B C D
Name % % %

Is on Sheet1 in the range A110

=IF(COUNTIF(Sheet1!A1:A10,M3),IF(COUNTIF(INDEX(She et1!B110,MATCH(M3,Sheet1!A1:A10,0),0),"0")=3,"Y es","No"),"-")

--
Biff
Microsoft Excel MVP


"Kiwi" wrote in message
...
Hi everyone

Hope this isn't a silly question (as think I may have made it harder in my
head than it needs to be! given all the formulas I'm putting in are
bringing
back errors!!)

I have one worksheet which gathers info from other sheets and has the
following info

A B C D
Name % % %

In another worksheet I have a list of names (column M say M3) next to the
names I want to put in a formula that will give me the following results:

If M3 is in worksheet 1 and the corresponding cells to that name in b, c
and
d are all 0% write Yes otherwise write No but if M3 doesnt match anything
on
the list write - .

Thanks as always

Kiwi

Try the below formula...

=IF(COUNTIF(Sheet1!A1:A100,M3),IF(SUMPRODUCT((Shee t1!A1:A100=M3)*
(Sheet1!B1100=0)),"No","Yes"),"-")

--
Jacob


"Kiwi" wrote:

Hi everyone

Hope this isn't a silly question (as think I may have made it harder in my
head than it needs to be! given all the formulas I'm putting in are bringing
back errors!!)

I have one worksheet which gathers info from other sheets and has the
following info

A B C D
Name % % %

In another worksheet I have a list of names (column M say M3) next to the
names I want to put in a formula that will give me the following results:

If M3 is in worksheet 1 and the corresponding cells to that name in b, c and
d are all 0% write Yes otherwise write No but if M3 doesnt match anything on
the list write - .

Thanks as always

Kiwi

Thank you both so much - both very helpful :-)
--
Thanks as always

Kiwi


"T. Valko" wrote:

Try this...

Assuming this data:

A B C D
Name % % %

Is on Sheet1 in the range A110

=IF(COUNTIF(Sheet1!A1:A10,M3),IF(COUNTIF(INDEX(She et1!B110,MATCH(M3,Sheet1!A1:A10,0),0),"0")=3,"Y es","No"),"-")

--
Biff
Microsoft Excel MVP


"Kiwi" wrote in message
...
Hi everyone

Hope this isn't a silly question (as think I may have made it harder in my
head than it needs to be! given all the formulas I'm putting in are
bringing
back errors!!)

I have one worksheet which gathers info from other sheets and has the
following info

A B C D
Name % % %

In another worksheet I have a list of names (column M say M3) next to the
names I want to put in a formula that will give me the following results:

If M3 is in worksheet 1 and the corresponding cells to that name in b, c
and
d are all 0% write Yes otherwise write No but if M3 doesnt match anything
on
the list write - .

Thanks as always

Kiwi


.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Kiwi" wrote in message
...
Thank you both so much - both very helpful :-)
--
Thanks as always

Kiwi


"T. Valko" wrote:

Try this...

Assuming this data:

A B C D
Name % % %

Is on Sheet1 in the range A110

=IF(COUNTIF(Sheet1!A1:A10,M3),IF(COUNTIF(INDEX(She et1!B110,MATCH(M3,Sheet1!A1:A10,0),0),"0")=3,"Y es","No"),"-")

--
Biff
Microsoft Excel MVP


"Kiwi" wrote in message
...
Hi everyone

Hope this isn't a silly question (as think I may have made it harder in
my
head than it needs to be! given all the formulas I'm putting in are
bringing
back errors!!)

I have one worksheet which gathers info from other sheets and has the
following info

A B C D
Name % % %

In another worksheet I have a list of names (column M say M3) next to
the
names I want to put in a formula that will give me the following
results:

If M3 is in worksheet 1 and the corresponding cells to that name in b,
c
and
d are all 0% write Yes otherwise write No but if M3 doesnt match
anything
on
the list write - .

Thanks as always

Kiwi


.