Writing Multiple IF Condition with Sum aggregate

[Anonymous]

Hi All,

I Need help with below requirement.

Data:  

Team	Process	Actual	Total	Standard
MTV	ECOM	40	100	50%
MTV	DTCKT	60	90	50%
LOAM	LOTICKT	90	160	50%
LOAM	LODRAFT	120	200	50%

Requirement : Want to Create a text box to show number of teams that met standard (i need the output in team level).

Condition: If 2 process of a team meets Standard( Actual/Total>=50%) then Teamstd count will be 1, else if only 1 team meets standard(>=50%) then it will be Teamstd counted as 0.

Explanation : In the above table for TEAM="MTV" only 1 team [ DTCKT Actual/Total >=50]  where as in TEAM="LOAM" for 2 process  Actual/Total <=50% so in the text box i want to show 1/2 (it says only 1 team out of 2 has 100% Success rate).

Please help.

Thanks in advance.

Arjin.

[sunny_talwar]

May be like this:

=Sum(Aggr(If(Sum(Aggr(If(Actual/Total >= 0.50, 1, 0), Team, Process)) = Count(Process), 1, 0), Team))/Count(DISTINCT Team)

[Anonymous]

Hi Sunny,

It works perfect, Thanks alot.

Best Regards,

Arjin.