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

CakzPrimz

New Member
Joined
Oct 6, 2017
Messages
42
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:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

WinteE

Well-known Member
Joined
Apr 8, 2007
Messages
605
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
Joined
Nov 7, 2006
Messages
1,003
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
Joined
Oct 6, 2017
Messages
42
Dear WinteE,

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

CakzPrimz

New Member
Joined
Oct 6, 2017
Messages
42
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
Joined
Nov 7, 2006
Messages
1,003
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
Joined
Oct 6, 2017
Messages
42
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
Joined
Nov 7, 2006
Messages
1,003
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
Joined
Oct 6, 2017
Messages
42
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

New Member
Joined
Oct 6, 2017
Messages
42
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,760
Messages
5,410,267
Members
403,305
Latest member
tray2014

This Week's Hot Topics

Top