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

 Other excel answers

 

 

You can find similar Excel Questions and Answer hereunder

1) How can I enter a text in a cell with subscript and superscript?

2) Sumifs with date range as criteria in Excel

3) How can I change the Marker size and Marker line color for all the series in a chart?

4) I have entered lot of comments in my WorkSheet - how do I have them show up when printed?

5) Formula does not calculate in Excel

6) How to disable ability to insert Rows and Columns in Excel (using VBA)?

7) Here an explanation about combo boxes and interfacing with your user in Excel VBA

8) Concatenate number with text but keeping in number format in Excel

9) How can I add and link a Chart Title to a cell value?

10) How do I disable the right click option for users in my WorkBook?

 

Here the previous and next chapter