Solved

Count cells of only visible rows within criteria range

Posted on 2014-04-29
11
476 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

932 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

10 Experts available now in Live!

Get 1:1 Help Now