I'm trying to take conditional counts on a filtered range. It's for a dynamic tool that others will be using, a table that updates values based on the filters applied to the variables.
Just the ranges I'm working on right now are PROGRAM NAME (I2:I7238), ENROLL DATE (M2:M7382) and GRADUATION DATE (O2:O7382). Right now, Clients Entered is reading correctly both filtered and unfiltered by using =SUMPRODUCT((I2:I7382="Program A")*(SUBTOTAL(103,OFFSET(I2,ROW(I2:I7382)-MIN(ROW(I2:I7382)),0)))) in the Clients Enrolled cells. The total Completed count cell uses =SUBTOTAL(102,O2:O7382). Now what I need is a formula that will give me the count of nonblank cells in O2:O7382 per program. Maybe it's something simple but I can't figure out how to get that column to return counts when Column I equals a specific value AND when Column O is nonblank for just the visible rows.
A shortened version with the current formulae and problems explained are in the example Excel sheet I've attached.
Any help is greatly appreciated, I'm under the gun on this. Thanks.
Bookmarks