Solved

Count cells of only visible rows within criteria range

Posted on 2014-04-29
11
478 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
  • 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 32

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now