AndreaAndrea
7 years agoQrew Trainee
How do I return one result from three fields?
I have 3 broker fields per order and need to figure out a way to return a total of commissions per broker when that broker's name is in any of the 3 fields. I am writing a report and this is the formula I was attempting to use in the calculated column. (I also filtered my report to show records only where this calculated column was greater than zero.)
If(
[Broker 1 Name]="ABC"
or
[Broker 2 Name]="ABC"
or
[Broker 3 Name]="ABC",
[Commission 1] + [Commission 2] + [Commission 3], 0)
This worked except that if there was more than one broker (who we will call "XYZ") but one of the names was the "ABC" that I was searching for, it still added the commission for XYZ into the formula. (understandably)
How do I tell it to only calculate Commissions 1, 2, or 3, when the broker is ABC? I tried to add in additional IF formulas:
If([Broker 1 Name]= ?ABC?,[Commission 1],0)
Thank you!
If(
[Broker 1 Name]="ABC"
or
[Broker 2 Name]="ABC"
or
[Broker 3 Name]="ABC",
[Commission 1] + [Commission 2] + [Commission 3], 0)
This worked except that if there was more than one broker (who we will call "XYZ") but one of the names was the "ABC" that I was searching for, it still added the commission for XYZ into the formula. (understandably)
How do I tell it to only calculate Commissions 1, 2, or 3, when the broker is ABC? I tried to add in additional IF formulas:
If([Broker 1 Name]= ?ABC?,[Commission 1],0)
+
If([Broker 2 Name]= ?ABC?,[Commission 2],0)
+
If([Broker 3 Name]= ?ABC?,[Commission 3],0)
,0)
but that did not work either. I'd like to have all of this info on one report rather than having to run a separate report for each broker field. I am open to suggestions!Thank you!