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

[CakzPrimz]

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.

 

[WinteE]

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]

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]

Dear WinteE,

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

 

[CakzPrimz]

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]

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]

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]

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]

Dear Johnny C,

I made a helper column as your instruction.
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]

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