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
Answer:
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
