Question about using Subtotal and CountIf in Excel Macro (VBA)

Question

Hi, I wrote a previous post (http://superuser.com/questions/151092/neophyte-question-about-using-subtotal-and-countif-in-excel), which I realized was not very clear. But I could not figure out how to edit it. My apologies for both blunders. Here then, is a much simpler version (I hope). I need to write a Macro to take the following type of data:

Part Type   Inspection Result
Battery Passed
Battery Passed
Lightbulb   Failed
Lightbulb   Passed
Lightbulb   Passed
Charger Failed

And come up with a (1) count in each category (i.e. number of Batteries) AND (2)a count of number in each category that Passed and number that Failed. This has to be done programmatically as I'm calling it from a third party product (Quality Center). I would appreciate any ideas. Here's a sketch of the way I started. The idea is to use Excel's Subtotal feature to get subtotals for each category. This will solve (1). The code is:

Sub Macro7()
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1, 2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=False
End Sub ' I got this by using record macro in Excel

I still need the number passed in each category so I did the following:

Part Type   Inspection Result   N. Passed in Category
Grand Count 6   6   
Battery Count   2   2   2
Battery Passed  
Battery Passed  
Lightbulb Count 3   3   1
Lightbulb   Failed  
Lightbulb   Passed  
Lightbulb   Passed  
Charger Count   1   1   0
Charger Failed  

The key formula is is where you see the first 2 for number passed in Battery Count. It is: =IF(ISTEXT(A3),COUNTIF(C4:C5,"=Passed"),"") I then simply "dragged" this cell down, hoping it would correctly fill in the rest of the cells. In a sense it did. You'll notice there are only results on the "Count" lines, thanks to the part about "ISTEXT" However, you'll see instantly that the number of passed Lightbulb's is reported as 1, not 2. When I look at the formula for this cell it's:=IF(ISTEXT(A6),COUNTIF(C7:C8,"=Passed"),"") The A6 and C7 are correct. The C8 should have been C9. Excel decidided that I always have 2 parts in each category, like the Battery category. How do I get it to adjust to the correct number? This very frustrating since the formulas I'm using seem similar to the subtotal formua (that provides the count) and that works fine.

If you look at the first cell with a 2 in it, it's =SUBTOTAL(3,B4:B5). The cell for the lightbulb count is: =SUBTOTAL(3,B7:B9) as it should be. It get 3 datum.

I hope this is a little clearer. As you can probably tell, Excel is not my forte. I'm just trying to present some data from Quality Center in Excel.

Thanks, Dave