If statement AND/OR/IfThen?

[Cleetus09]

I'm not too familiar with the AND/OR/IfThen functions or maybe a better way to go about it, but what i'm trying to do is score two categories of Customers 2S and 3S and their levels.

2S customers must have 1 level 2 and 1 other level
3S Must have 1 level 1 and 1 other level

If the conditions are not met, I would like to have them score a 0.

If the conditions are met, a 1 or 2 or even just a value other than 0 would be work.

So just not sure how to proceed. Almost as if there is a If/Then type... matching the 2S or 3S then going through the criteria to meet.

I've attached a sample workbook

Any help is appreciated

[63falcondude]

I'm not sure that I follow.

Are the desired results included in the sample workbook? If not, I recommend including those and then re-uploading your sample.

[Cleetus09]

desired results are something showing that the main condition is met with another, it can be anything (i'm not picky and can change it), but could be:

Conditions met = 1
Conditions not met = 0

[63falcondude]

I am going against my better judgement to insist that you follow the request from post #2.

Try this in F2:

=--OR(AND(B2="2S",D2>0,SUM(C2,E2)>0),AND(B2="3S",C2>0,SUM(D2,E2)>0))

Make sure that you convert the numbers in column E from Text to actual numbers.

[Cleetus09]

I've re-uploaded the worksheet.

With the desired results.

[63falcondude]

Thank you.

The formula from post #4 gives the desired results shown from the workbook in post #5 (except for in cell F6) after converting the "Text" numbers in column E to actual numbers.

To convert the numbers, you'll have to un-merge cell B10, then you can highlight column E > Text to Columns > Finish

Can you explain why, given the criteria from post #1, F6 should be 1?

[Bo_Ry]

Is this right?

F2
=SUMPRODUCT((B2={"3S","2S"})*(C2:D2>0))*(SUMPRODUCT(--C2:E2)>1)

[Cleetus09]

Can you explain why, given the criteria from post #1, F6 should be 1?

Because of 2 Level 1's. they have the minimum 1 Level 1 plus 1 other.

[63falcondude]

Oh so the one "other" doesn't have to be in a different level?

In that case, assuming that 2S and 3S are the only Types, you can use this:

=--AND(IF(B2="2S",D2,C2)>0,SUM(C2:E2)>1)

[Cleetus09]

Is this right?

F2
=SUMPRODUCT((B2={"3S","2S"})*(C2:D2>0))*(SUMPRODUCT(--C2:E2)>1)

Almost, it comes up a-ok for everything except for if there is a 2S customer with a Level 1 and a Level 3
I didn't notice those until the formula (reattached book1, Name 7,8 for examples)

3S is all good

[Cleetus09]

it does not, can be two of the same or more...

[63falcondude]

it does not, can be two of the same or more...

Understood. Have you tried the formula from post #9 after converting the Text numbers to actual numbers?

[Cleetus09]

Understood. Have you tried the formula from post #9 after converting the Text numbers to actual numbers?

I did but same issue with Bo, the Name 7 and Name 8 lines don't work for the 2S customer. The rare occasion they have Level 1 or upgraded a level 2 to a Level 1.

[63falcondude]

I did but same issue with Bo, the Name 7 and Name 8 lines don't work for the 2S customer. The rare occasion they have Level 1 or upgraded a level 2 to a Level 1.

In post #1 you said:

2S customers must have 1 level 2 and 1 other level

Names 7 and 8 don't have a level 2.

[Bo_Ry]

How about

=((B2="3S")*(C2>0)+(B2="2S"))*(SUMPRODUCT(--C2:E2)>1)

[Cleetus09]

In post #1 you said:


Names 7 and 8 don't have a level 2.

It skipped my mind that it was possible... until the formula brought it to light. I could manually change them if needed as its just 3 out of 200.

[Cleetus09]

How about

=((B2="3S")*(C2>0)+(B2="2S"))*(SUMPRODUCT(--C2:E2)>1)

I think that does it!

[63falcondude]

Thanks for the rep!