CountIF only on visible rows

David Donker

New Member
Joined
Apr 23, 2013
Messages
16
Hi guy's,

The title of this speaks for itself. At the moment I'm using checkboxes to hide some rows, and I want to count the visible rows with a certain value.

Originally I used this formula:
=COUNTIF(Totaal!B3:B10000;"V")

But this also counts the hidden rows. How can I only count the visible rows with this formula?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Thanks for your quick response!

Correct me if I'm wrong, but they sum their numbers in the example, and I need to count rows with a certain value. I'm not that experienced with Excel, so probably I'm missing something, but I don't exactly know how this can help me :(
 
Upvote 0
It's the same sort of setup for a count or a sum, try this formula for your specific problem

=SUMPRODUCT((Totaal!B3:B10000="V")*SUBTOTAL(3;OFFSET(Totaal!B3;ROW(Totaal!B3:B10000)-ROW(Totaal!B3);0)))
 
Upvote 0
The above formula does do in so far what I want that it works the same as an CountIF, but it's still counting the hidden rows.
Perhaps it's not working because I don't use an auto-filter, but my own made checkboxes.

See below how the checkbox works.

Code:
Private Sub CheckBox1_Click()
    
    Dim i As Integer
    If CheckBox1 = True Then
    
        For i = 3 To 1000
            If Worksheets("Totaal").Cells(i, 2) = "V" Then
                Worksheets("Totaal").Cells(i, 1).EntireRow.Hidden = False
            End If
        Next i
        
    Else
        
        For i = 3 To 1000
            If Worksheets("Totaal").Cells(i, 2) = "V" Then
                Worksheets("Totaal").Cells(i, 1).EntireRow.Hidden = True
            End If
        Next i
    
    End If
    
End Sub
 
Upvote 0
Hi,

Does this help?....

UnHidden/UnFiltered

Excel Workbook
ABCDEF
1DataCount1Count2Count3
2w555
3V
4X
5Y
6Z
7V
8W
9Y
10V
11V
12W
13X
14W
15Z
16V
17
Sheet10


Hidden (row 10)

Excel Workbook
ABCDEF
1DataCount1Count2Count3
2w544
3V
4X
5Y
6Z
7V
8W
9Y
11V
12W
13X
14W
15Z
16V
17
Sheet10


Filtered

Excel Workbook
ABCDEF
1DataCount1Count2Count3
2w500
8W
12W
14W
17
Sheet10


I hope that helps.

Ak
 
Upvote 0
Ha, Thx a lot guy's.

I noticed a small difference in the two given formulas.

Instead of =SUMPRODUCT((Totaal!B3:B10000="V")*SUBTOTAL(3;OFFSET(Totaal!B3;ROW(Totaal!B3:B10000)-ROW(Totaal!B3);0)))
I needed to use =SUMPRODUCT((Totaal!B3:B10000="V")*SUBTOTAL(103;OFFSET(Totaal!B3;ROW(Totaal!B3:B10000)-ROW(Totaal!B3);0)))

It works great now, thank for all the help!
 
Upvote 0
Sorry, I was thinking filtering - as you say, the 100 series ignores both hidden and filtered rows - my suggestion only ignores filtered rows...
 
Upvote 0
It seems I still don't really get it. I can change the value for the Column B without a problem, but if I try the same for the other Columns, I always get the output ''0'' which is the wrong output.

So first I have the formula: =SUMPRODUCT((Totaal!B3:B10000="V")*SUBTOTAL(103;OFFSET(Totaal!B3;ROW(Totaal!B3:B10000)-ROW(Totaal!B3);0)))

If I try the same for other rows, i'm using this: =SUMPRODUCT((Totaal!C3:C10000="<14")*SUBTOTAL(103;OFFSET(Totaal!C3;ROW(Totaal!C3:C10000)-ROW(Totaal!C3);0)))
Or this: =SUMPRODUCT((Totaal!F3:F10000="1")*SUBTOTAL(103;OFFSET(Totaal!F3;ROW(Totaal!F3:F10000)-ROW(Totaal!F3);0)))

Meaby it's because this formula only works for text? Or else, what am I doing wrong?
 
Upvote 0
If you are looking at numbers then 102 is probably better (COUNT to count numbers only rather than COUNTA).....and you shouldn't have quotes around "<14", that's a COUNTIF type syntax, not done that way in other functions, so first formula would be

=SUMPRODUCT((Totaal!C3:C10000=<14)*SUBTOTAL(102;OFFSET(Totaal!C3;ROW(Totaal!C3:C10000)-ROW(Totaal!C3);0)))

and similarly in the second one - in COUNTIF if you use "1" as the criterion it counts both numeric and text 1s but elsewhere "1" only counts text, so you probably need to lose the quotes like

=SUMPRODUCT((Totaal!F3:F10000=1)*SUBTOTAL(102;OFFSET(Totaal!F3;ROW(Totaal!F3:F10000)-ROW(Totaal!F3);0)))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top