If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|