If Question

[Kiwi]

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

 

[T. Valko]

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(Sheet1!B110,MATCH(M3,Sheet1!A1:A10,0),0),">0")=3,"Yes","No"),"-")

 

[Jacob Skaria]

Try the below formula...

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

 

[Kiwi]

Thank you both so much - both very helpful

 

[T. Valko]

You're welcome. Thanks for the feedback!