Countif- Visible Cells in Filter mode

[aabbasi]

Hi:

Could someone please let me know the solution for the following problem:

I have values in cell B2:B50 with values such as Quality, Eng, Purchasing etc. When i go in Auto filter based on Coulmn A2:A50 (with July only) and count "Quality" manually under B2:B50, the answer is 26. But if I write formula Countif(B2:B50, "Quality") I get answer 41.

Is there a way to use Countif function, if I am in the Autofilter mode so as it counts only that rows which are visible under Autofilter and not ALL rows.

Thank you

 

[mgirvin]

Dear Aladin,

Thank you for helping me to learn some new tricks! You are amazing!

 

[Default300]

I have 2 questions directly related to what has gone before, and so the answer should be relevant to others reading this post.

I hope I am not hijacking this post. If I am, I'll happily repost elsewhere.


I want to achieve eactly the same goal (ie count or sum only visible cells in a single column on a filtered worksheet).

But I want to do so in VBA, rather than place a formula on a worksheet.


The formula posted by Aladin Akyurek...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality"))

... works perfectly on the worksheet.

[?] How would I achieve it in VBA?


I have tried using Application.WorksheetFunction, but I don't know:
(1) Do I have to specify "Application.WorksheetFunction." for each of the nested functions?
OR
(2) Will one "Application.WorksheetFunction." do for entire formula?
OR
(3) Do I have to restructure some of it with VBA equivalent functions?
OR
(4) Do I break it down into elements (ie "un-nest" it), store each element in a variable, and then "re-nest" it?

Have tried all variations without success!!


Second Question is re Subtotal Function and 3 vs 103 as First Argument:

[?] For AdvancedFilter (as opposed to AutoFilter) should I use 3 or 103 or something else?

Thanks.

 

[RoryA]

As far as SUBTOTAL is concerned, all filters are the same.
For the VBA, can you give a more specific example of what you are trying to count/sum? If it's just all the visible cells in a column, then you can use the SpecialCells method to restrict a range to the visible cells only.

 

[titekinou]

Hey! I tried to use the formula but it doesnt work, it says there is an error in the formula in subtotal.. I cant understand. Is this the only way to count with the filter?
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality"))

thanks!

 

[Colin Legg]

The formula gives the correct result in the worksheet? To literally use that formula in VBA you have to evaluate it.

For example:

Sub example()
    Dim vResult As Variant
 
    vResult = Sheet1.Evaluate("=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50=""Quality""))")
End Sub

Change Sheet1 to the codename of your worksheet.

 

[Default300]

Rory, thanks for responding.

The specific case that I want to Count/Sum currently is as follows:

Full Range (Unfiltered) to Sum and/or Count is:
E7:E891

Cell Values in This Range are:
either "1" or "" (ie blank)

The Filter(s) operate on up to 3 other columns

I want to Count the Number of Rows which are Visible AND Value ="1"


The overall purpose is related to a ScrollRow manoeuvre which needs to take account of which rows are visible at the time.

In this particular case, either Count or Sum would do since the Value 1, but it would be great to know the syntax for both, and/or your suggestion of using xlSpecialCells.

I had considered using xlSpecialCells, but my preference is to refer to the cells but not to select cells on the worksheet. I couldn't work out the syntax to do this.

Thanks.

PS. Thanks to others who have posted while I was composing.

Colin, "Evaluate" look like an interesting / useful tool.
Does it have drawbacks? Like slowing down application etc. No need to reply if I'm dragging topic off on a tangent. Can google it later to check out opinions.

 

[RoryA]

For the sum in that specific case, you could of course just use:

Application.Sum(Range("E2:E58").SpecialCells(xlCellTypeVisible))

and similarly for count. If you wanted to check a condition on each cell, you could simply loop through the visible cells:

Function VisSumIf(rngIn As Range, crit) As Double
   Dim rngCell As Range
   Dim dblTemp As Double
   For Each rngCell In rngIn.SpecialCells(xlCellTypeVisible)
      With rngCell
         If .Value = crit Then dblTemp = dblTemp + .Value
      End With
   Next rngCell
   VisSumIf = dblTemp
End Function

 

[Default300]

Colin, thanks for responding.

Re: DataType of Variable (that will hold the result of the Evaluate function).

Is it important to Dimension it "As Variant"?

Or can it be "As Integer", "As Long" etc, if I'm sure that the sum/count will fit the restrictions of that datatype?

TIA

 

[Colin Legg]

Colin, thanks for responding.

Re: DataType of Variable (that will hold the result of the Evaluate function).

Is it important to Dimension it "As Variant"?

Or can it be "As Integer", "As Long" etc, if I'm sure that the sum/count will fit the restrictions of that datatype?

TIA

What if the evaluation returns an error value? You can declare as a double if you want, but then you will need to add VBA error handling.

 

[Default300]

Thanks again.

I'll probably use that nice succinct version for this situation.
It needs to respond almost instantly as the ScrollRow has to change immediately after the User (of the Userform) selects an item from a ComboBox list.

Two small follow-up questions (a habit I learned from that TV detective, Columbo):

(1) To change the first solution to Count, do I simply replace the word "Sum" with "Count"?
(2) Is "Application." important before "Sum". If so, should it be "Application.WorksheetFunction" or not?

Thanks.