Alternatively this can be done with Power Query M code as well. This works with legacy Excel such as Excel 2013.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NumwithEndTiera = Table.SelectRows( Source, each [State] ="end" and [EndTier]="a"),
    #"Merged Queries" = Table.NestedJoin(Source,{"Num"},NumwithEndTiera ,{"Num"},"Table1",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Num"}, {"Num.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each ([Num.1] <> null)),
    #"Removed Columns" = Table.SelectRows(Table.RemoveColumns(#"Filtered Rows",{"Num.1"}), each [Tier]<>"a"),
    #"Removed Columns1" = Table.RowCount(Table.Distinct(Table.RemoveColumns(#"Removed Columns",{"State", "Tier", "EndTier"})))
in
    #"Removed Columns1"