Solved

Count cells of only visible rows within criteria range

Posted on 2014-04-29
11
481 Views
Last Modified: 2014-04-29
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.
Appreciate your help.
Berry
count-visible-TorF.xlsx
0
Comment
Question by:Berry Metzger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 40029633
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40029684
SUBTOTAL functions will only count/sum visible rows.

Thanks
Rob H
0
 
LVL 23

Expert Comment

by:NBVC
ID: 40029715
If you open the workbook, you will see that the OP understands that, and just mistyped the post!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40029719
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40029725
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
 

Author Comment

by:Berry Metzger
ID: 40029745
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40029753
Let me know when you try my formula.
gowflow
0
 
LVL 23

Expert Comment

by:NBVC
ID: 40029782
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40029797
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
 
LVL 23

Expert Comment

by:NBVC
ID: 40030303
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
 

Author Comment

by:Berry Metzger
ID: 40030323
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question