Help with Nested IF statement for multiple outcomes

[Ginge]

Hi there,

I'm trying to categorise records in my spreadsheet by looking at values in a number of other cells. This is so that I can then use a macro to pull them across into other sheets.

In a nutshell, there are three cells in each row that display whether a particular party has an interest in the product for that record. So there is a total of 8 different combinations of interests that I am trying to capture.

Company A Only – I4=”NO”, P4=”YES”, S4=”NO”
Company B Only – I4=”NO”, P4=”NO”, S4=”YES”
Company C Only - I4=”YES”, P4=”NO”, S4=”NO”
No Interest - I4=”NO”, P4=”NO”, S4=”NO”
Company A & Company B - I4=”NO”, P4=”YES”, S4=”NO”
Company A & Company C - I4=”YES”, P4=”YES”, S4=”NO”
Company B & Company C - I4=”YES”, P4=”NO”, S4=”YES”
Company A, Company B & Company C - I4=”YES”, P4=”YES”, S4=”YES”

I'm currently planning to just do a huge nested IF statement with a numeric value (1-8) being returned depending on which of the 8 categories the record meets, which i know is probably not ideal, but I'm not exactly a guru when it comes to other formulas.

Any advice would be greatly appreciated.

Cheers

 

[PCL]

A possibility could be
Assuming data in column B to E from row 3 to 10
B C D E
Company A Only  ”NO” ”YES” ”NO”
Company B Only  ”NO” ”NO” ”YES”
Company C Only ”YES” ”NO” ”NO”
No Interest ”NO” ”NO” ”NO”
Company A & Company B ”NO” ”YES” ”NO”
Company A & Company C ”YES” ”YES” ”NO”
Company B & Company C ”YES” ”NO” ”YES”
Company A, Company B & Company C ”YES” ”YES” ”YES


C14 =SUMPRODUCT((C3:C10=H4)*(D3:D10=O4)*(E3:E10=R4)*ROW(B3:B10))
C15 =INDEX(B1:B10;C14;1)

Final result in C15

 

[Ginge]

A possibility could be
Assuming data in column B to E from row 3 to 10
B C D E
Company A Only  ”NO” ”YES” ”NO”
Company B Only  ”NO” ”NO” ”YES”
Company C Only ”YES” ”NO” ”NO”
No Interest ”NO” ”NO” ”NO”
Company A & Company B ”NO” ”YES” ”NO”
Company A & Company C ”YES” ”YES” ”NO”
Company B & Company C ”YES” ”NO” ”YES”
Company A, Company B & Company C ”YES” ”YES” ”YES


C14 =SUMPRODUCT((C3:C10=H4)*(D3:D10=O4)*(E3:E10=R4)*ROW(B3:B10))
C15 =INDEX(B1:B10;C14;1)

Final result in C15

Sorry, my initial post might not have been clear. There are only 3 columns that need to be checked to get the 8 different combinations (I, P and S). The 8 values I want to assign are the combinations of whether nobody has an interest in the product, everybody does, only one party does, or some combination of the parties does.

That's why I was initially thinking of a nested if statement. I need to create a way of checking which parties have an interest (even if the answer is nobody), because they will each need to be categorised.

Ginge

 

[fhqwgads]

maybe this? i assumed each case 1-8 was in the order you listed

=IF(AND(I4="NO", P4="YES", S4="NO"),1,IF(AND(I4="NO", P4="NO", S4="YES"),2,IF(AND(I4="YES", P4="NO", S4="NO"),3,IF(AND(I4="NO", P4="NO", S4="NO"),4,IF(AND(I4="NO", P4="YES", S4="YES"),5,IF(AND(I4="YES", P4="YES", S4="NO"),6,IF(AND(I4="YES", P4="NO", S4="YES"),7,IF(AND(I4="YES", P4="YES", S4="YES"),8,""))))))))

if i understand it column P is company A, S is B and I is C? i got confused when you said

Company A Only – I4=”NO”, P4=”YES”, S4=”NO”...
Company A & Company B - I4=”NO”, P4=”YES”, S4=”NO”

hope i got the companies right

 

[Ginge]

maybe this? i assumed each case 1-8 was in the order you listed

=IF(AND(I4="NO", P4="YES", S4="NO"),1,IF(AND(I4="NO", P4="NO", S4="YES"),2,IF(AND(I4="YES", P4="NO", S4="NO"),3,IF(AND(I4="NO", P4="NO", S4="NO"),4,IF(AND(I4="NO", P4="YES", S4="YES"),5,IF(AND(I4="YES", P4="YES", S4="NO"),6,IF(AND(I4="YES", P4="NO", S4="YES"),7,IF(AND(I4="YES", P4="YES", S4="YES"),8,""))))))))

if i understand it column P is company A, S is B and I is C? i got confused when you said

hope i got the companies right

Works like a charm! Thankyou so much, you've saved me hours of fumbling around trying to get it right!

Ginge

 

[jtakw]

Hi,

There's no need for a Long Nested IF statement to produce the logic you require, I've dealt with similar situations several times, you Only need to test each cell Once.

In my following sample, if you Only want the result in a Number ( 1 to 8 ), use G4 formula, the meanings for each number from 1 to 8 can be explained by the G5 formula.

To produce the result with an Actual Description, use G5 formula, formula uses P3, S3, I3 for the Company Names (assumes your company names are in P3, S3, and I3 for Company A, B, C respectively), OR, we can "hard-code" the Company Names in the formula.

Book1
	F	G	H	I	P	S
3				Company C	Company A	Company B
4	Result as Number	7		Yes		Yes
5	Result in Description	Company B Company C
Sheet155

Cell Formulas
Range	Formula
G4	=SUM(1,IF(P4="Yes",1,0),IF(S4="Yes",2,0),IF(I4="Yes",4,0))
G5	=CHOOSE(SUM(1,IF(P4="Yes",1,0),IF(S4="Yes",2,0),IF(I4="Yes",4,0)),"No Interest",P3,S3,P3&" "&S3,I3,P3&" "&I3,S3&" "&I3,P3&" "&S3&" "&I3)

Let me know if you need further explanation and/or some tweak to the formulas.