[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Count cells of only visible rows within criteria range

Posted on 2014-04-29
11
Medium Priority
?
489 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Accepted Solution

by:
gowflow earned 2000 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 31

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 31

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 31

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

656 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