Solved

# Count cells of only visible rows within criteria range

Posted on 2014-04-29
469 Views
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.
Berry
count-visible-TorF.xlsx
0
Question by:Berry Metzger
• 4
• 4
• 2
• +1

LVL 23

Expert Comment

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

SUBTOTAL functions will only count/sum visible rows.

Thanks
Rob H
0

LVL 23

Expert Comment

If you open the workbook, you will see that the OP understands that, and just mistyped the post!
0

LVL 29

Accepted Solution

gowflow earned 500 total points
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

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

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

Let me know when you try my formula.
gowflow
0

LVL 23

Expert Comment

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

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

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

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

### Suggested Solutions

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.