Solved

Count cells of only visible rows within criteria range

Posted on 2014-04-29
11
469 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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
SUBTOTAL functions will only count/sum visible rows.

Thanks
Rob H
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Berry Metzger
Comment Utility
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
Comment Utility
Let me know when you try my formula.
gowflow
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 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

12 Experts available now in Live!

Get 1:1 Help Now