## Subtotal and sumif combination help in Excel

Alright, I need help combining two formulas.

What we need is to subtotal a range based on a criteria and we need that subtotal to change when the range is filtered.

We have two formulas that work seperately but we need to combine them into one.

=SUBTOTAL(9,Summary!H3:H39)

=SUMIF(Summary!F3:F39,B35,Summary!H3:H39)

B35 = The name we're trying reference
Column F is the column that would hold that name
Column H has the values we want to sum

Look at this example on how to create your own personalised lookup function

Function PersoLookup(LValue, Lrange As Range, col As Long)

Dim output As String
Dim r As Range

output = ""
For Each va In Lrange
If va = LValue Then
output = output & " " & va.Offset(0, col - 1)
End If
Next va

PersoLookup = output

End Function

Save this function in a module and then use the =PersoLookup(E2;A2:A13;2) in the cell of your choice. Put the searched value into E2 (in that case)

(for formulas, depending on your country, you might have to change ; with , or the opposite