Count cells of only visible rows within criteria range

Hello Experts,
A formula (spreadsheet attached) that returns 0 needs fixing to count cell entries matching "T" or "F" and for rows selected in a criteria range.  COUNTIF() works fine, but not only for visible rows after AutoFiltering.
Berry
count-visible-TorF.xlsx
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(INDEX(C:C,Z1):INDEX(C:C,Z2),ROW(INDEX(C:C,Z1):INDEX(C:C,Z2))-MIN(ROW(INDEX(C:C,Z1):INDEX(C:C,Z2))),0,1))*(INDEX(C:C,Z1):INDEX(C:C,Z2)="T")*(INDEX(A:A,Z1):INDEX(A:A,Z2)>=\$Z\$1))*(INDEX(A:A,Z1):INDEX(A:A,Z2)<=\$Z\$2)

and

=SUMPRODUCT(SUBTOTAL(3,OFFSET(INDEX(C:C,Z1):INDEX(C:C,Z2),ROW(INDEX(C:C,Z1):INDEX(C:C,Z2))-MIN(ROW(INDEX(C:C,Z1):INDEX(C:C,Z2))),0,1))*(INDEX(C:C,Z1):INDEX(C:C,Z2)="F")*(INDEX(A:A,Z1):INDEX(A:A,Z2)>=\$Z\$1))*(INDEX(A:A,Z1):INDEX(A:A,Z2)<=\$Z\$2)
0
Finance AnalystCommented:
SUBTOTAL functions will only count/sum visible rows.

Thanks
Rob H
0
Commented:
If you open the workbook, you will see that the OP understands that, and just mistyped the post!
0
Commented:
you need to add a helper column in Column V to show 1 for visible row and 0 for hidden row then use sumproduct like in the attached file the formula is in cell Z3

here is the formula
=SUMPRODUCT((INDIRECT("C"&Z1&":C"&Z2)="T")*(INDIRECT("V"&Z1&":V"&Z2)=1))

you do the same for F
=SUMPRODUCT((INDIRECT("C"&Z1&":C"&Z2)="F")*(INDIRECT("V"&Z1&":V"&Z2)=1))

gowflow
count-visible-TorF.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Note I added col V but you can put the helper in any column and simply hide it. I did this for you to see the results.
gowflow
0
Lean process improvement consultantAuthor Commented:
The formula you wrote with range references of "C:C" causes a circular reference when placed into the same column it is counting.  Moving the formula to another column returns 0.  Any ideas?
0
Commented:
Let me know when you try my formula.
gowflow
0
Commented:
Try this formula in C207:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2),ROW(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2))-MIN(ROW(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2))),0,1))*(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2)="T"))

you can copy it across.

Similarly for the F:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2),ROW(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2))-MIN(ROW(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2))),0,1))*(INDEX(C\$1:C\$207,\$Z\$1):INDEX(C\$1:C\$207,\$Z\$2)="F"))
count-visible-TorF.xlsx
0
Commented:
Now I understand what you want to do.
You want to replace all the formulas in row 1 and 2 to count T and F for the visible columns in their respective columns.

I have adapted the formulas in row 1 and 2 take a look.

You get obviously a circular reference as Z1 = 1 Z1 should start from the first row that contain T or  F or your data it should be row 4 then you have no problem.

Let me know
gowflow
count-visible-TorF.xlsx
0
Commented:
So by accepting the answer you did, you are saying that the solution I provided did not work?  To me it looks like it works just as you asked!
0
Lean process improvement consultantAuthor Commented:
I went with your solution gowflow. Nice work!
You saved me hours trying for a solution that works.
I changed the helper column V formula from =SUBTOTAL(102,B5) to =SUBTOTAL(103,B5) because entries in B:B are text (no biggie).  Also, I added 5 (number of header rows above the first row with T or F entries) to the value in Z1 and Z2 using "Z1+ROWS(hdr_rows) and pointed your formulas to this new range now hidden in AA1 & AA2.  This way, the user can still enter a column A reference range to filter and the chart  will reflect visible unfiltered Nos. in the title.  The results are attached for reference.

In another thread, I will ask you for a formula that returns from text in column B "ALL" if visible rows under "Team" in col B contain more than 1 unique text value, or returns "1" if all visible rows contain only "1" or returns "2" when all visible rows only contain "2" after filtering on "Team"
Berry
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.