Sum and Countifs with multiple Criteria problem

Hi All,

Can anyone to help me to check what is wrong with my formula, I want to get the exact count of tested to the Step Counter Tab (column Total per Resource). Unfortunately, I am not getting the same total count in total per resource and tested. I am using below formula

Please see the screenshots the results from excel.

=SUM(COUNTIFS('MDD_Activity v.02'!$O:$O,'Step Counter'!$A2,'MDD_Activity v.02'!$P:$P,'Step Counter'!B$1,'MDD_Activity v.02'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS(MCIv3.1!$O:$O,'Step Counter'!$A2,MCIv3.1!$P:$P,'Step Counter'!B$1,MCIv3.1!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('General Features'!$O:$O,'Step Counter'!$A2,'General Features'!$P:$P,'Step Counter'!B$1,'General Features'!$K:$K,{"Passed";"Failed";"Blocked"}), COUNTIFS('MDD_Dining v.01'!$O:$O,'Step Counter'!$A2,'MDD_Dining v.01'!$P:$P,'Step Counter'!B$1,'MDD_Dining v.01'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('Web Check-in'!$N:$N,'Step Counter'!$A2,'Web Check-in'!$O:$O,'Step Counter'!B$1,'Web Check-in'!$J:$J,{"Passed";"Failed";"Blocked"}),COUNTIFS('MDD_BarAndLounge v.01'!$O:$O,'Step Counter'!$A2,'MDD_BarAndLounge v.01'!$P:$P,'Step Counter'!B$1,'MDD_BarAndLounge v.01'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('Login v2.1'!$O:$O,'Step Counter'!$A2,'Login v2.1'!$P:$P,'Step Counter'!B$1,'Login v2.1'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('DreamElite v3.1'!$O:$O,'Step Counter'!$A2,'DreamElite v3.1'!$P:$P,'Step Counter'!B$1,'DreamElite v3.1'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('MMB v.01'!$O:$O,'Step Counter'!$A2,'MMB v.01'!$P:$P,'Step Counter'!B$1,'MMB v.01'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('Admin Portal'!$O:$O,'Step Counter'!$A2,'Admin Portal'!$P:$P,'Step Counter'!B$1,'Admin Portal'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('VC VK vTest Summary Tab.jpg2.3'!$O:$O,'Step Counter'!$A2,'VC VK v2.3'!$P:$P,'Step Counter'!B$1,'VC VK v2.3'!$K:$K,{"Passed";"Failed";"Blocked"}), COUNTIFS('MDD_Facilities v.02'!$O:$O,'Step Counter'!$A2,'MDD_Facilities v.02'!$P:$P,'Step Counter'!B$1,'MDD_Facilities v.02'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('MDD_iCafe v.01'!$O:$O,'Step Counter'!$A2,'MDD_iCafe v.01'!$P:$P,'Step Counter'!B$1,'MDD_iCafe v.01'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('MDD_Shorex v.01'!$O:$O,'Step Counter'!$A2,'MDD_Shorex v.01'!$P:$P,'Step Counter'!B$1,'MDD_Shorex v.01'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('MDD_Casino v.01'!$O:$O,'Step Counter'!$A2,'MDD_Casino v.01'!$P:$P,'Step Counter'!B$1,'MDD_Casino v.01'!$K:$K,{"Passed";"Failed";"Blocked"}),COUNTIFS('MDD_MyWallet v.01'!$O:$O,'Step Counter'!$A2,'MDD_MyWallet v.01'!$P:$P,'Step Counter'!B$1,'MDD_MyWallet v.01'!$K:$K,{"Passed";"Failed";"Blocked"}))Step Counter Tab.jpg

Hi Admin,

Sorry, I already updated my excel version and please see attached my sample worksheet.

My issue with the sample worksheet is the count of total tested in Step Counter tab was not the same Total Tested in the Summary tab. I used the below formula.

=SUM(COUNTIFS(Value!$B:$B,'Step Counter'!$A2,Value!$C:$C,'Step Counter'!B$1,Value!$A:$A,{"Passed","Failed","Blocked"}),COUNTIFS('Value 2'!$B:$B,'Step Counter'!$A2,'Value 2'!$C:$C,'Step Counter'!B$1,'Value 2'!$A:$A,{"Passed";"Failed";"Blocked"}),COUNTIFS('Value 3'!$B:$B,'Step Counter'!$A2,'Value 3'!$C:$C,'Step Counter'!B$1,'Value 3'!$A:$A,{"Passed";"Failed";"Blocked"}),COUNTIFS('Value 5'!$B:$B,'Step Counter'!$A2,'Value 5'!$C:$C,'Step Counter'!B$1,'Value 5'!$A:$A,{"Passed";"Failed";"Blocked"}))

Hi all,

Can anyone to check my formula please?

Hi Sir,

I already corrected the checking column in the Summary tab, but the total count of tested in the Step Counter is still not the same in the Summary Tab.
I really don't know what is the problem to my formula.

Hi Pepe,

Sorry for being impatient and yes I understand this will not happen again. Thanks!

Originally Posted by XOR LX

Your formula in cell B3 of the Step Counter sheet is referencing cell B2 instead of cell B1.

Regards

Thanks, Xor! I got the same count in my sample sheet, however, in my actual file, I am still not getting the same count. is it okay for you if I can share my actual file to you?

Hi Sir Xor,

I created a dummy file and data, but the content its almost the same in my actual file. As you can see in the Step Counter tab, the total counted (Total Per Resource) is 7038, while in the Test Summary Tab, the total tested is 6461. I really don't know what is the problem in my formula.
I am hoping you will find the cause of error in my formula