Couting non numeric items from a filtered list

I have a list of locations, and status indicator for each. e.g.

A1 B1
Leeds R
Leeds R
Leeds A
London R
London G
London R

I can filter these by location, so that I can see all of the results for Leeds etc.
What I would now like to do is create a formula that counts up the number of R's or A's for the location that has been filtered.

I have tried a simple 'countif' but it counts all of the R's in the entire range.

Can anyone help ?

Thanks in advance

You can do this, but you need an array formula to do it. See this link (http://www.cpearson.com/excel/ArrayFormulas.aspx) for some help, or look up array formulas in your Excel help.

The formula would be: =SUM(IF($A$2:$A$100="Leeds",1,0)*IF($B$2:$B$100="R",1,0))

You'll need to hit <Ctrl><Shift><Enter> when you enter the formula. Note that you don't have to hardcode "Leeds" or the "R". You could point them to cells as well.

thanks Mill, but I just want to add up the number of R's that exist in the range based on the location that I have filtered on. As such, I don't need the location in the formula.

Hope this makes sense.

Just realised. I have made a mistake with the above. All ranges should start from row 2 (I need to leave room for my autofilter arrows).

If anyone can help it would be greatly appreciated.

If you have Excel 2003 or later, you can get what you want using lists.
Enter the data, then instead of using autofilter, click Lists->Create List.

Next, pull up the List toolbar. Click the Toggle Total Row button.

Now, for any combination that you choose between the Location and the Status, the total row will count up the number of entries for you. Note that you'll need to filter on both columns in order for the count to work properly.

Thanks Mill,
Unfortunately I have Excel2000.
Is this something that can be done using a formula ?

managed to acquire the following (for anyone interested):

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A7)-ROW(A2),,1)),--(B2:B7="R"))

That's a very clever formula... since someone has been generous enough to furnish you with it I hope that you have taken the time to understand how it works! My adapted version (very similar):

=-SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A7)-ROW(A2),,1)),-(B2:B7="R"))
Out of interest, why do you specifically want to count R's based on a manually filtered range? What advantage does that give you over the more traditional approach of effectively stipulating criteria within a formula such as the example Mill gave in #2?