Shorten these Or conditions

[mahmed1]

Hi All,

How can I shorten these 2 formulas

=IF(OR([@[Team Manager]]="Matt Gritton Team Blue2000",[@[Team Manager]]="Matt Gritton Team Green2000",[@[Team Manager]]="Academy2000"),"CS_ACADEMY",IF(OR([@[Team Manager]]="Matt Gritton Team Blue2001",[@[Team Manager]]="Matt Gritton Team Green2001",[@[Team Manager]]="Academy2001"),"CS_ACADEMY",IF(OR([@[Team Manager]]="Matt Gritton Team Blue2003",[@[Team Manager]]="Matt Gritton Team Green2003",[@[Team Manager]]="Academy2003"),"CS_ACADEMY",IF(OR([@[Team Manager]]="Matt Gritton Team Blue4000",[@[Team Manager]]="Matt Gritton Team Green4000",[@[Team Manager]]="Academy4000"),"COSC_ACADEMY",[@[Team Manager]]))))

=IFERROR(SUMPRODUCT(--(tbl_Agent_Results[AHT]),--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),(--(tbl_Agent_Results[Team Manager]="MATT GRITTON TEAM GREEN4000")+--(tbl_Agent_Results[Team Manager]="MATT GRITTON TEAM BLUE4000")+--(tbl_Agent_Results[Team Manager]="Academy4000")))/SUMPRODUCT(--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),(--(tbl_Agent_Results[Team Manager]="MATT GRITTON TEAM GREEN4000")+--(tbl_Agent_Results[Team Manager]="MATT GRITTON TEAM BLUE4000")+--(tbl_Agent_Results[Team Manager]="Academy4000"))),"")

I have the same multiple or conditions and was hoping I can make this slicker and more easier to condense it down as the condition could grow

Many Thanks

 

[Eric W]

For the first one, I'd recommend using a lookup table instead of listing all the values in the formula. Here's a sample of what that would look like.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	a	b	c	d	Team Manager					Team Manager	Code
2	1	2	3	Matt Gritton Team Blue2000	Matt Gritton Team Blue2000					Matt Gritton Team Blue2000	CS_ACADEMY
3										Matt Gritton Team Green2000	CS_ACADEMY
4										Academy2000	CS_ACADEMY
5										Matt Gritton Team Blue2001	Academy2001
6										Matt Gritton Team Green2001	Academy2001
7
Sheet4

Cell Formulas
Range	Formula
D2	D2	=IFERROR(VLOOKUP([@[Team Manager]],$J$2:$K$6,0),[@[Team Manager]])

For the second formula, again I'd use a lookup table, as Mike suggested. Instead of using

=SUMPRODUCT( . . . (--(tbl_Agent_Results[Team Manager]="MATT GRITTON TEAM GREEN4000")+--(tbl_Agent_Results[Team Manager]="MATT GRITTON TEAM BLUE4000")+--(tbl_Agent_Results[Team Manager]="Academy4000")) . . .)

try:

=SUMPRODUCT(. . .--ISNUMBER(MATCH((tbl_Agent_Results[Team Manager],$J$2:$J$4,0)) . . .)

where J2:J4 has the list of valid values.

 

[mahmed1]

Thank you so much
Much much appreciated