How to get a max value less than X where column A = smth...

[Gedeon Kh]

I have data set on excel worksheet like this:

name | date | amount
NM_1 | 01/01/2016 | 100
NM_2 | 01/01/2016 | 5000
NM_1 | 01/02/2016 | 70
NM_1 | 01/03/2016 | 50
NM_3 | 01/04/2016 | 400


My goal is to find the amount where name = NM_1 and date <= 05/01/2016:

How can I find the value in VBA ??

 

[Paoolo]

Why not use AutoFilter and subtotal formula instead?
Is VBA code necessary? It would be something like:

    rng.AutoFilter Field:=1, Criteria1:="NM_1"
    rng.AutoFilter Field:=2, Criteria1:= _
        "<05/01/2016", Operator:=xlAnd

 

[Gedeon Kh]

But will this work within function ????

 

[AliGW]

You asked for VBA. Are you now saying that you want a formula? If so, try:

=SUMIFS(C$1:C$5,A$1:A$5,"NM_1",B$1:B$5,"<05/01/2016")

 

[Gedeon Kh]

You asked for VBA. Are you now saying that you want a formula? If so, try:

=SUMIFS(C$1:C$5,A$1:A$5,"NM_1",B$1:B$5,"<05/01/2016")

I tried, but this function didn't work, the problem was in "<05/01/2016" part.