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:

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,

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
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top