# Thread: How to get Pass and Fail count from Excel sheet

1. ## How to get Pass and Fail count from Excel sheet

I have Excel result sheet with column "Result" populated with "PASS" or "FAIL" values.Now I want to open this sheet and count the total occurances of PASS and FAIL and write the total counts in another sheet.I tried using Range,Subtotal.but that didn't help me.
Can anyone help me on this please?

2. ## Re: How to get Pass and Fail count from Excel sheet

You can use COUNTIF to count the number of cells that have a specific value:
"=COUNTIF(G1:G200, "PASS")"
"=COUNTIF(G1:G200, "FAIL")"

So, you can put two summary cells at the bottom of your spreadsheet (COUNTIF PASS, COUNTIF FAIL) and then link those results to other tracking spreadsheets if you want.

I use this in conjunction with COUNTA to get the percentage of PASS &amp; FAIL test cases per worksheet. Use COUNTA to get the number of populated cells in a column and use the COUNTIF PASS &amp; COUNTIF FAIL results to get the numbers of PASS &amp; FAIL tests. Then just do a simple division of the [subset/total] cells to get the percentages of PASS &amp; FAIL cells based on the total number of cells.
"=COUNTA(G1:G200)"

3. ## Re: How to get Pass and Fail count from Excel sheet

Are you doing this thru QTP./.??

I would suggest to record a simple macro(as mentioned in the previous post) excel to do this and then run the macro from QTP..(if at all you want to use QTP..

else, if the number of rows is very dynamic , then export the data and then retrieve the values for comparison and set the counter...(not very clean but..)

4. ## Re: How to get Pass and Fail count from Excel sheet

Hi pearl_qa,

I could get pass , fail count and total cells count populated with either PASS or FAIL using below statements.
Formula= "=COUNTIF(C1:C200," &amp; Chr(34) &amp; "PASS" &amp; Chr(34) &amp; ")"
total="=COUNTA(C1:C200)"

But I couldn't get the PASS or FAIL percentage based on these 2 values since QTP takes Formula and Total as strings.
It is not returning count even as a string value to convert that into integer and apply formula. And also I want to directly print these values in another Excel file but not in the same Excel workbook from which we are taking result values.When I tried to print the count in another Excel file by opening it in the same Fucntion , formula value is returning 0 value.is there any work around to cut and paste these values into new Excel file?
Can you provide me the code if you have already done this?

5. ## Re: How to get Pass and Fail count from Excel sheet

Hi ren,

Unfortunately, I'm not sure how you'd integrate the Excel formula into a QTP script. I thought you already had the results in Excel and were just looking for the formula to use in Excel.

Excel formulas start with = so maybe if you take out the extra = and the quotes in your example it would work:

xlBook.cells(2, 4).Formula = COUNTIF(C1:C200, "PASS")
xlBook.cells(2, 4).Value = COUNTIF(C1:C200, "PASS")
StrFormula=COUNTIF(C1:C200, "PASS")

6. ## Re: How to get Pass and Fail count from Excel sheet

You can use excel functions through vbscript (but not VBA ones). Remember you will need to define whole range though. You can test in Excel VBE using Application.worksheetfunction......

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.