Formula

[vaidmohammed]

I am having 2 formulae for deriving UAT and Production defects... following they are :

FOR UAT ::
=COUNTIFS(Data!$B$2:$B$50000,"Defect",Data!$D$2:$D$50000,"UAT",Data!$E$2:$E$50000,$A2,Data!$F$2:$F$50000,"Y")

say the answer come as 121

FOR PRODUCTION ::
=COUNTIFS(Data!$B$2:$B$50000,"Defect",Data!$D$2:$D$50000,"Production",Data!$E$2:$E$50000,$A3,Data!$F$2:$F$50000,"Y")

say the answer come as 97

I want a single formula considering the above 2 formulae to derive the sum result i.e. (121 + 97 = 218).

please advice....

 

[GraH - Guido]

What about "+"?
COUNTIFS(Data!$B$2:$B$50000,"Defect",Data!$D$2:$D$50000,"UAT",Data!$E$2:$E$50000,$A2,Data!$F$2:$F$50000,"Y") + COUNTIFS(Data!$B$2:$B$50000,"Defect",Data!$D$2:$D$50000,"Production",Data!$E$2:$E$50000,$A3,Data!$F$2:$F$50000,"Y")

 

[John Jairo V]

Hi, to all!

Or...
=SUMPRODUCT(COUNTIFS(Data!$B$2:$B$50000,"Defect",Data!$D$2:$D$50000,{"UAT";"Production"},Data!$E$2:$E$50000,$A2:$A3,Data!$F$2:$F$50000,"Y"))

Blessings!