Count the Y's in a filtered column that contains Y and N
In Column A there could be a date.
In Column B there could be another date. Column C has either Tom or Janet in it. Column D says if Column B is greater than Column A insert Y, if Column B is less than Column A insert N. Now here is my problem, when I filter Column A or B to only show non-blanks, my Column D is now a filtered Column with both Y's and N's. Now I want to filter only for Janet's records and count her Y's. I want to leave the total number of records returned but only count the Y's. When I do the COUNTIF function for Y, it returns ALL of the Y's within the range of Column D, including Tom's. How do I count only the visible Y's for Janet. This is hard to represent graphically without inserting the entire sheet. Hopefully you get the drift. Thx in advance...TommyB -- TommyB |
Count the Y's in a filtered column that contains Y and N
=SUMPRODUCT((RngD="Y")*(SUBTOTAL(3,OFFSET(RngC,ROW (RngC)-MIN(ROW(RngC)),,1))))
"TommyB" wrote: In Column A there could be a date. In Column B there could be another date. Column C has either Tom or Janet in it. Column D says if Column B is greater than Column A insert Y, if Column B is less than Column A insert N. Now here is my problem, when I filter Column A or B to only show non-blanks, my Column D is now a filtered Column with both Y's and N's. Now I want to filter only for Janet's records and count her Y's. I want to leave the total number of records returned but only count the Y's. When I do the COUNTIF function for Y, it returns ALL of the Y's within the range of Column D, including Tom's. How do I count only the visible Y's for Janet. This is hard to represent graphically without inserting the entire sheet. Hopefully you get the drift. Thx in advance...TommyB -- TommyB |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com