# SUM based on 3 criteria but only the latest date - Excel Function

#### CakzPrimz

##### New Member
Dear MrExcel

I have to find the value of the latest date, based on 3 criteria:

Column A = range criteria 1 (A1:A10)
Column B = range criteria 2 (B1:B10)
Column C = range criteria 3 (C1:C10)
Column D = range of the value/number (D1:D10)
Column E = range of the date (E1:E10)

A12 = criteria 1
A13 = criteria 2
A14 = criteria 3

How to write the formula in A15 to get the number in column D, only for the latest date?

Thanks so much, I really-really hope that somebody would like to help me.

Regards

EDIT: Based on criteria 1, criteria 2, criteria 3 at once.

Last edited by a moderator:

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### WinteE

##### Well-known Member
Try this:

This formula only works when the result is a numeric value

=SUMPRODUCT((A1:A10=A12)*(B1:B10=A13)*(C1:C10=A14)*(E1:E10=MAX(E1:E10))*(D1:D10))

#### Johnny C

##### Well-known Member
Which version of Excel do you have? if you have Excel 2016 you can use the MAXIFS function.

You can make a helper column in F1:F10 with the formula =A1&"~"&B1&"~"&C1&"~"&E1

This would get you the maximum date for those conditions.
=MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14) gets you the latest date for your conditions.

You can then use MATCH to get you the row where the max date is
=MATCH(A12&"~"&A13&"~"&A14&"~"&MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14),F1:F10,0)

Then use INDEX or OFFSET to get it using that value.
=INDEX(D1:D10,MATCH(A12&"~"&A13&"~"&A14&"~"&MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14),F1:F10,0))
or
=OFFSET(\$D\$1,MATCH(A12&"~"&A13&"~"&A14&"~"&MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14),F1:F10,0)-1,0)
You need the -1 if using OFFSET as there's no row 0 above the top of your data.

Both will give an error if there's no row with the conditions so you would need an IFERROR wrapped around it.
=IFERROR(INDEX(D1:D10,MATCH(A12&"~"&A13&"~"&A14&"~"&MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14),F1:F10,0)),"No match found")

#### CakzPrimz

##### New Member
Dear WinteE,

Thanks so much for your help and assistance. I really appreciate it.
It works !!! Problem solved.
Again, thank you.

#### CakzPrimz

##### New Member
Dear Johnny C,

I use Office 365, I will try your formulas. I will let you know soon.
Thank so much

Best regards

#### Johnny C

##### Well-known Member
There is a risk with =SUMPRODUCT((A1:A10=A12)*(B1:B10=A13)*(C1:C10=A14)*(E1:E10=MAX(E1:E10))*(D1:D10)) in that it will only work if the max date exists for the combination of A12-A14.
If MAX(E1:E10) is today and the latest date for the combination you want is before today it won't work as MAX(E1:E10) would be today's date.

#### CakzPrimz

##### New Member
Dear Johnny C,

This would get you the maximum date for those conditions.
=MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14) gets you the latest date for your conditions.

You were right, it gave me the latest date

But this formula, gave me #value . I believe because I don't use column F at all.
Then use INDEX or OFFSET to get it using that value.
=INDEX(D1:D10,MATCH(A12&"~"&A13&"~"&A14&"~"&MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14),F1:F10,0))

Other formulas of yours refer to column F.

Thank you

#### Johnny C

##### Well-known Member
See my original message
You can make a helper column in F1:F10 with the formula =A1&"~"&B1&"~"&C1&"~"&E1 in F1 and copy it down

#### CakzPrimz

##### New Member
Dear Johnny C,

But when I copy this formula below

You can then use MATCH to get you the row where the max date is
=MATCH(A12&"~"&A13&"~"&A14&"~"&MAXIFS(E1:E10,A1:A10,A12,B1:B10,A13,C1:C10,A14),F1:F10,0)

it turns into #N/A

Thanks

#### CakzPrimz

##### New Member
Dear WinteE,

It works only for one criteria, date in data source mostly duplicate.
Could I upload an attachment? so you could clearly understand the structure?

Thank you