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

 

[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

I tried to create 2 named range and use that for the 1st formula with no luck

CS_Academy_List

Matt Gritton Team Blue2000
Matt Gritton Team Green2000 etc....

COSC_Academy_List

Matt Gritton Team Blue4000
Matt Gritton Team Green4000 etc....

=IF([@[Team Manager]]= CS_Academy_List, “CS_Academy”, IF([@[Team Manager]]= COSC_Academy_List,”COSC_Academy, [@[Team Manager]]))

I tried 2 do same with sumproduct but failed that too
=IFERROR(SUMPRODUCT(--(tbl_Agent_Results[AHT]),--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),- -(tbl_Agent_Results[Team Manager]= COSC_Academy_List)) /SUMPRODUCT(--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),
--(tbl_Agent_Results[Team Manager]=“COSC_Academy”)),””)

 

[mahmed1]

I tried to wrap in count ifs and sum ifs but can’t get it to work

 

[mahmed1]

I tried this too for the 1st formula

=If(Countifs(CS_Academy_List,[@[Team Manager]])>0,”CS_Academy”, If(Countifs(COSC_Academy_List,[@[Team Manager]])>0,”COSC_Academy”,[@[Team Manager]]))

no clue how to incorporate this sumifs into the sum product to make it smaller and more dynamic

 

[mahmed1]

Any formula gurus who can help me

thank you so much

much appreciated

 

[mahmed1]

My attempts of shortening both formulas without having much luck

The reason why i want to make it dynamic is because my or criteria could extend

=If(Countifs(CS_Academy_List,[@[Team Manager]])>0,”CS_Academy”, If(Countifs(COSC_Academy_List,[@[Team Manager]])>0,”COSC_Academy”,[@[Team Manager]]))

=IFERROR(SUMPRODUCT(- -(tbl_Agent_Results[AHT]),--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5), ISNUMBER(MATCH(tbl_Agent_Results[Team Manager],COSC_Academy_List)))
/
SUMPRODUCT(--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),
ISNUMBER(MATCH(tbl_Agent_Results[Team Manager],COSC_Academy_List))),””)

 

[mahmed1]

Another attempt




=IFERROR(SUMPRODUCT(- -(tbl_Agent_Results[AHT]),--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),countifs([Team Manager],COSC_Academy_List))
/
SUMPRODUCT(--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),countifs([Team Manager],COSC_Academy_List)),””)

 

[mahmed1]

Bump anyone

 

[mikerickson]

i don't use structured references so this syntax might be off, but you could use ISNUMBER(MATCH(@[Team Manager], someRange,0)) to see if the team manager is in the list located at someRange.

 

[mahmed1]

i don't use structured references so this syntax might be off, but you could use ISNUMBER(MATCH(@[Team Manager], someRange,0)) to see if the team manager is in the list located at someRange.

Firstly I want to thank u so much for responding back to me - I appreciate it so much

based on the formula you have given - would you say i need to amend to something like this (ive not tested yet)


=IFERROR(SUMPRODUCT(- -(tbl_Agent_Results[AHT]),--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5), ISNUMBER(MATCH(@[Team Manager],COSC_Academy_List,0)))
/
SUMPRODUCT(--(tbl_Agent_Results[contactsHandled]),--(tbl_Agent_Results[Date_]=I$5),
ISNUMBER(MATCH(@[Team Manager], COSC_Academy_List,0))),””)

only thing i can see is that its only looking at 1 cell where as i need to check team manager column to see if it exists with in the list range

il give it a go