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

 

[fairwinds]

You would have to calculate also the blanks and deduct:

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

Ctrl + Shift + Enter


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

Ctrl + Shift + Enter


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

Book2
	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 3	Step 1
8	Test case 3	Step 2
9	Test case 4	Step 1	fail
10	Test case 4	Step 2	fail
11
12			blank	1
13			fail	2
14			pass	1
Sheet1

 

[subhah]

- If any one of the step has blank "Result", then the entire test case should be ignored. Example: test case # 2 can be ignored since the results for all steps are blank

- If any one of the step is a "Fail", then the entire test case should be a "Fail". Example: Test case # 3

- If all test steps are "Pass", then the test case should be a "Pass". Example: Test case # 1 has 3 steps and the result is "Pass" for all steps which means test case # 1 is a "Pass"

 

[subhah]

thank you very much

 

[cnvoleti]

<form name="formCb755237"><input *******="window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);" value="Copy Formula" name="btCb873980" type="button">
</form><center>

<tbody> </tbody>

<tbody>
</tbody>

</center>
Hi,

I have 7 different values to be pulled and not able to use the formula to find exact count. please help.

<tbody>

Passed

Failed

Not Started

Out Of Scope

Not Applicable

Untested

Blocked

<colgroup><col></colgroup><tbody>
</tbody>

</tbody>

 

[cnvoleti]

Passed

Failed

Not Started

Out Of Scope

Not Applicable

Untested

Blocked

<tbody>
</tbody>

Hi, I have above 7 different values to get the count and coming correctly using the formulas.

 

[cnvoleti]

Passed

Failed

Not Started

Out Of Scope

Not Applicable

Untested

Blocked

<tbody>
</tbody>

Hi, I have above 7 different values to get the count and coming correctly using the formulas.

Thank you .. I have solved this with the same formula... after tweaking a bit.. it worked out