Excel 2007 : COUNTIF and COUNTIFS on Visible Cells Only

OK Folks, I am back once again with an issue I would really appreciate some assistance with. Basically, I have a spreadsheet that a variety of countif and countifs functions that I am using to analyze data with. I need these to count only visible cells after I apply the auto filter. Please see below:

=COUNTIF(E2:F500, "Y")
=COUNTIFS(K2:K500, "Y", F2:F500, "Y")

These are the two examples I need to sove for and the rest of the functions have different cell range references but if I can get a solution on these two, I will be able to trnslate them to the rest f the worksheet. This is what I have tried:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E2,ROW(E2:E500)-ROW(E2),0)),(E2:E500="Y")+0)

It works but I cannot get it to count multiple columns. When I put the range of this formula as E2:F500 in all approprate places I get the #VALUE error. Can someone give me the right formula to use for both of my above scenarios. Your assistance is greatly appreciated.

Thank you very much! I apologize for posting the additional thread. I utilized your formula and it works however when I filter from any other item other than the first person in the list it does not work and returns blank cells for all the formulas. I have attached a copy of the worksheet with sample data in it. Any helppassback tracker.xlsx would be greatly appreciated.

Try the Subtotal function - pick count or count A and it will return the number when you filter.

I have embedded the subtotal in with the sumproduct function. The formula I have is working but when I filter for any one other than the first fileter list choice the cells with the formulas in them return a blank cell.

AH! That is exactly what is happening, I have the formulas in Row 2 from Columns 0 onward. If I wanted to put these formulas on Sheet 2 or whatever I name that worksheet to in the same workbook, how would I adjust the formula? Sorry for being so needy.

Thank you very very much. Your formula just confirmed what I entered and evrything is working perfectly.