Hello,

I am trying to apply a COUNTIF function to only visible cells in a selected range. Here's the regular COUNTIF formula that I'm working with.

=COUNTIF(INDEX(TABLE1,0,MATCH($B8,TABLE1[#Headers],0)),VLOOKUP(18,$AF$7:$AG$28,2,FALSE))

Column B in the current sheet contains dates, which are also present in the header row in TABLE1. This countif formula does the following:

1) Uses INDEX(TABLE1,0,MATCH($B8,TABLE1[#Headers],0)) to define the range as the column in TABLE1 with a header that matches the date in $B8.
2) Uses VLOOKUP(18,$AF$7:$AG$28,2,FALSE) to define the search term through a VLOOKUP of a small table in the current sheet.

The formula works just fine. However, I'm unsure of how to get it to ignore hidden rows. I know that you can do this with simpler functions using SUBTOTAL, and I have a VBA solution that works but is extremely slow. Any ideas?

Thanks for your help.