Counting test case results using formula

[subhah]

I have my test cases in below format and I would like to calculate # of test cases passed or failed using formula. Can anyone pls. help?

-------------------------------------------------
Test case # Step # Result
-------------------------------------------------
Test case 1 Step 1 pass
Test case 1 Step 2 pass
Test case 1 Step 3 fail
Test case 2 Step 1 pass
Test case 2 Step 2 pass
Test case 2 Step 3 pass
Test case 2 Step 4 pass
Test case 3 Step 1 fail
Test case 3 Step 2 fail

I need below result using formula:
# of test cases - Pass = 1
# of test cases - Fail = 2

-Subha

 

[Krishnakumar]

Hi Subha,

Welcome to board!

Try something like..

=COUNTIF(TestCasesRange,"Pass")

 

[subhah]

Thanks for the reply Kris!

=COUNTIF(TestCasesRange,"Pass") - This formula will include test steps too. but, i want to count only test cases..

 

[fairwinds]

Hi,

Try:

D12: =SUM(IF(FREQUENCY(IF(C2:C10=C12,IF(A2:A10<>"",MATCH(A2:A10,A2:A10&"",0))),ROW(A2:A10)-MIN(ROW(A2:A10))+1),1))
Confirmed with Ctrl + shift + enter

D13: =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))-D12

Book1
	A	B	C	D
1
2	Test case 1	Step 1	pass
3	Test case 1	Step 2	pass
4	Test case 1	Step 3	fail
5	Test case 2	Step 1	pass
6	Test case 2	Step 2	pass
7	Test case 2	Step 3	pass
8	Test case 2	Step 4	pass
9	Test case 3	Step 1	fail
10	Test case 3	Step 2	fail
11
12			fail	2
13			pass	1
Sheet4

 

[subhah]

Wonderful! Thanks so....much..

 

[subhah]

I have another problem..

Actually, the test case # column has only numbers and not texts. so, the above formula does not work. It throws #N/A.

Can you pls. help?

 

[Aladin Akyurek]

I have another problem..

Actually, the test case # column has only numbers and not texts. so, the above formula does not work. It throws #N/A.

Can you pls. help?

Control+shift+enter...

=SUM(IF(FREQUENCY(IF($A$2:$A$10<>"",IF($C$2:$C$10=C12,MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0))),ROW($A$2:$A$10)-ROW($A$2)+1),1))

and copy down.

 

[subhah]

Thanks so much..it helped me a lot..

 

[subhah]

My test data is like below and the above formula gives me wrong results for "Pass". Need help again...

-------------------------------------------------------
Test case # Step # Result
-------------------------------------------------------
1 Step 1 pass
1 Step 2 pass
1 Step 3 pass
2 Step 1
2 Step 2
2 Step 3
2 Step 4
3 Step 1 pass
3 Step 2 fail
3 Step 3 pass
4 Step 1 fail
4 Step 2 fail


Actual result by formula:
fail 2
pass 2

Expected result:
fail 2
pass 1

 

[Aladin Akyurek]

My test data is like below and the above formula gives me wrong results for "Pass". Need help again...

-------------------------------------------------------
Test case # Step # Result
-------------------------------------------------------
1 Step 1 pass
1 Step 2 pass
1 Step 3 pass
2 Step 1
2 Step 2
2 Step 3
2 Step 4
3 Step 1 pass
3 Step 2 fail
3 Step 3 pass
4 Step 1 fail
4 Step 2 fail


Actual result by formula:
fail 2
pass 2

Expected result:
fail 2
pass 1

Which records are valid and why in order to arrive at 1 for "pass"?