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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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
 

Forum statistics

Threads
1,089,485
Messages
5,408,536
Members
403,213
Latest member
Rvbicon

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top