Count the Y's in a filtered column that contains Y and N

T

TommyB

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
 
T

Teethless mama

=SUMPRODUCT((RngD="Y")*(SUBTOTAL(3,OFFSET(RngC,ROW(RngC)-MIN(ROW(RngC)),,1))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top