Noob in distress.
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), ENTRY DATE (M2:M7382) and EXIT DATE (O2:O7382). I have dynamic breakdown table that should look like this when all data is unfiltered:
PROGRAM #Clients Enrolled #Clients Graduated
Program A_______ 1441_____ 1139
Program B_______ 4510_____ 3025
Program C_______ 1369_____ 926
Program D_______ 31 _____ 23
Not Registered____ 30 _____ 0
TOTAL__________ 7381_____ 5113
I hope that table is legible. 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.
Any help is greatly appreciated, especially if you can answer within the next hour. Thanks.
Bookmarks