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.

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.

Thanks for the help OliverScheurich. Is there a way that this can be done if we have different domains in my data?

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.

Thank you OliverScheurich. Unfortunately both of the formulas are showing some error. Any suggestions?

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),IF(RIGHT(C2,3)="usa",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="usa")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="usa")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="usa")),5)+1),IF(RIGHT(C2,3)="zzz",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="zzz")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="zzz")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="zzz")),5)+1),IF(RIGHT(C2,3)="edv",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="edv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="edv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="edv")),5)+1),"")))))Both formulas work flawlessly and as expected in my sheet. I've expanded the nested IF formula for 5 domains and it returns the intended results. Below i've highlighted the initial criteria of each IF statement for explanation. =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),IF(RIGHT(C2,3)="usa",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="usa")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="usa")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="usa")),5)+1),IF(RIGHT(C2,3)="zzz",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="zzz")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="zzz")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="zzz")),5)+1),IF(RIGHT(C2,3)="edv",IF(MOD(SUMPRODUCT(N(RIGHT($C$2:C2,3)="edv")),5)=0,"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="edv")),5),"Agent "&QUOTIENT(SUMPRODUCT(N(RIGHT($C$2:C2,3)="edv")),5)+1),"")))))

Excel Formula | Microsoft Community Hub

18 Replies

zayn_mir
Copper Contributor
Feb 01, 2023
Thanks for the help OliverScheurich. Is there a way that this can be done if we have different domains in my data?
- OliverScheurich
  Gold Contributor
  Feb 01, 2023
  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
    Copper Contributor
    Feb 07, 2023
    Thank you OliverScheurich. Unfortunately both of the formulas are showing some error. Any suggestions?

OliverScheurich

Gold Contributor

Jan 27, 2023

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.

Forum Discussion

Excel Formula

18 Replies

Resources