Help with Excel 'if' Function

Question

Hi All, I need some help with an excel formula (IF) - i'll describe what I am trying to achieve, if you think there is a better way around this then please let me know.

In order for a customer to classified as a certain sub group they need to hold two core products (labelled in columns D and E) as well as two other products (columns F to K) if they do then obviously I want the argument to return TRUE if not then FALSE

At the moment I can't get the formula to work... Can someone help me??

This is what I have so far

=IF(D2:E2="Y" G2:K2=2*"Y","YES","NO")

Its obviously returning invalid argument...any help?

Cheers

Answer 1

Thanks so much for that ! It works!!!

Now....is there a way to 'hide' the value of the formula if any of its cells have 0 / no values??

i.e. say D2 and E2 had nothing in them then the formula would say nothing instead of "NO" or is that getting too difficult...

Cheers

Answer 2

Hi Guys,

 

Thanks very much for your replies.

 

So the formula would need to report YES if;

 

Both columns D and E were 'Y' AND any 2 of columns F, G, H, I, J and K are "Y"

So for e.g.

 

D   E   F   G   H   I   J   K

1   Y   Y   Y   Y   Y   Y   Y   Y   -> Would need to report "YES"

2   Y   Y   N   Y  N   N   N   Y   -> Would also need to report "YES"

3   N   Y   N   N  Y   N   Y   N   -> Would need to report "NO"

4   Y   Y   N   N   N   N   N   Y  -> Would need to report "NO"

 

Thanks for your help with this (again)!

Try this formula:

=IF(AND(D2="Y",E2="Y",COUNTIF(F2:K2,"Y")>=2),"YES","NO")

Change the >= to = if you require exact 2 of the columns F to K to have a "Y".

Hope this helps / Lars-Åke

Answer 3

Hi Guys,

Thanks very much for your replies.

So the formula would need to report YES if;

Both columns D and E were 'Y' AND any 2 of columns F, G, H, I, J and K are "Y"

So for e.g.

D   E   F   G   H   I   J   K

1   Y   Y   Y   Y   Y   Y   Y   Y   -> Would need to report "YES"

2   Y   Y   N   Y  N   N   N   Y   -> Would also need to report "YES"

3   N   Y   N   N  Y   N   Y   N   -> Would need to report "NO"

4   Y   Y   N   N   N   N   N   Y  -> Would need to report "NO"

Thanks for your help with this (again)!

Answer 4

Hello,

there is a disconnect between your verbal description and the attempted formula you posted.

D2 to E2 is two cells. So far, so good.

Then what? Column F or column G? Up to column K? Column G all the way to column K is 5 cells? Column G and column K is just two cells (as the formula above provides).

You need to be precise. Details matter.

D2, E2, G2 and K2 have been covered above. D2, E2, G2 up to K2 would be

=IF(SUM(COUNTIF(INDIRECT({"D2:E2","G2:K2"}),"Y"))=7,"Y","N")

Again. Details matter. Excel can be very particular about that. If you need column F in the mix, you need to adjust the formula accordingly.

cheers, teylyn

Answer 5

Hi,

Try this

=IF(SUM(COUNTIF(INDIRECT({"D2:E2","G2","K2"}),"Y"))=4,"Y","N")