I have the following Excel table:

And I want to create a metric that will tell me the Count of Rows where Country=US:

The answer should be "2" of course. How would I make the number "2" link to the Raw tab with the Filter Country="US" applied? To get the number I can do: =COUNTIF(Table2[Country],"US"), but how would I link and have the filter applied?

CodePudding user response：

You can use this formula:

=AGGREGATE(3,1,Table2[country])

It will return the number of visible rows in the filtered table.

If you filter by CA it will return 1 ... that would not fit to the to text left of it

CodePudding user response：

I don't believe there is a built-in way to do this without diving into the VBA code, but here is a solution for the above:

1. You can use the table formula: =COUNTIF(Table2[Country],"US").
2. In that cell right-click and add a hyperlink in "This Document" and enter in the location of the cell that was clicked, for example B2.
3. The following VBA code is what I used:

(general)
Sub Macro1()
    Sheets("Raw").Select
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
        "US"
End Sub

(worksheet)
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$B$2" Then
        Call Macro1
         End If
End Sub

CodePudding user response：

=COUNTIF(Raw!B2:B4,"US")