Counting test case results using formula

S

subhah

New Member
Joined
Nov 6, 2007
Messages
7
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
 
Sort by date Sort by votes
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
ABCD
1
2Test case 1Step 1pass
3Test case 1Step 2pass
4Test case 1Step 3fail
5Test case 2Step 1pass
6Test case 2Step 2pass
7Test case 3Step 1
8Test case 3Step 2
9Test case 4Step 1fail
10Test case 4Step 2fail
11
12blank1
13fail2
14pass1
Sheet1
 
Upvote 0
- 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"
 
Upvote 0
<form name="formCb755237"><input *******="window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);" value="Copy Formula" name="btCb873980" type="button">
</form><center>[TABLE="align: center"]
<tbody>[TR]
[TD="bgcolor: #0c266b, colspan: 5"][/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 5"][TABLE="width: 100%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 5"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #d4d0c8, align: center"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8, align: center"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff, align: left"][/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 5"][/TD]
[/TR]
</tbody>[/TABLE]
</center>
Hi,

I have 7 different values to be pulled and not able to use the formula to find exact count. please help.
[TABLE="width: 87"]
<tbody>[TABLE="width: 136"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Passed
[/TD]
[/TR]
[TR]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Not Started[/TD]
[/TR]
[TR]
[TD]Out Of Scope
[/TD]
[/TR]
[TR]
[TD]Not Applicable
[/TD]
[/TR]
[TR]
[TD]Untested
[/TD]
[/TR]
[TR]
[TD]Blocked[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 136"]
<tbody>[TR]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Not Started[/TD]
[/TR]
[TR]
[TD]Out Of Scope[/TD]
[/TR]
[TR]
[TD]Not Applicable[/TD]
[/TR]
[TR]
[TD]Untested[/TD]
[/TR]
[TR]
[TD]Blocked[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi, I have above 7 different values to get the count and coming correctly using the formulas.
 
Upvote 0
cnvoleti said:
[TABLE="width: 136"]
<tbody>[TR]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Not Started[/TD]
[/TR]
[TR]
[TD]Out Of Scope[/TD]
[/TR]
[TR]
[TD]Not Applicable[/TD]
[/TR]
[TR]
[TD]Untested[/TD]
[/TR]
[TR]
[TD]Blocked[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Thank you .. I have solved this with the same formula... after tweaking a bit.. it worked out
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Newer user looking to link multiple tables that autopopulate data based on results
Replies
5
Views
128
Jaytam49
J
D
  • Solved
Dynamic range in formula
Replies
2
Views
137
Deutz
D
Eawyne
  • Question Question
[VBA] - IF & AND statements together
Replies
7
Views
125
Tupe77
T
A
  • Question Question
Excel is getting slower as I add more sheets
Replies
3
Views
117
Micron
M
A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
773
dmt32
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top