Forum Discussion

zayn_mir's avatar
zayn_mir
Copper Contributor
Jan 27, 2023

Excel Formula

Hi I need help,

 

I need the emails of different people with the same domain to be assigned to 1 Agent. If there are more than 5 people ( I want the next 5 ) to be assigned to a different Agent.

18 Replies

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor

      zayn_mir 

      =IF(RIGHT(C2,3)="csv",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="csv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="csv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="csv")),5)+1),IF(RIGHT(C2,3)="abc",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="abc")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="abc")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="abc")),5)+1),""))

      You are welcome. I'd apply a nested IF formula for multiple domains.

       

  • zayn_mir 

    =IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="uvw")),5)+1)

    You can try this formula if there is only one domain ("uvw" in the example) in your data.

     

Resources