Sumifs for greatest date and other criteria

[martinli]

Hi,

I have a data set with multiple columns. See below.

[TABLE="width: 888"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Order[/TD]
[TD]Rcvr-Order[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Pct.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]332673[/TD]
[TD]901822653[/TD]
[TD]7[/TD]
[TD]2015[/TD]
[TD]1.00[/TD]
[TD][/TD]
[TD="align: right"]901822653[/TD]
[/TR]
[TR]
[TD]332673[/TD]
[TD]901822653[/TD]
[TD]5[/TD]
[TD]2016[/TD]
[TD]0.80[/TD]
[TD="align: right"]332673[/TD]
[TD="align: right"]1.84[/TD]
[/TR]
[TR]
[TD]332673[/TD]
[TD]901822653[/TD]
[TD]2[/TD]
[TD]2018[/TD]
[TD]0.04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]332673[/TD]
[TD]901822656[/TD]
[TD]5[/TD]
[TD]2016[/TD]
[TD]0.04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]332673[/TD]
[TD]901822657[/TD]
[TD]5[/TD]
[TD]2016[/TD]
[TD]0.04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]332673[/TD]
[TD]901822658[/TD]
[TD]5[/TD]
[TD]2016[/TD]
[TD]0.04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]332673[/TD]
[TD]901822720[/TD]
[TD]5[/TD]
[TD]2016[/TD]
[TD]0.04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 888"]
<colgroup><col span="5"><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 888"]
<colgroup><col span="5"><col><col></colgroup><tbody></tbody>[/TABLE]
I have a formula on G4 =SUMIFS($E:$E,$A:$A,F4,$B:$B,G3) that returns 1.84 as an example. However I want it to return only the greatest Month/Year Combo. In this case 2, 2018 for a total of 0.04. How Can I incorporate a max function within a sumif or is there an alternative way.

Thanks in advance!

 

[Eric W]

Welcome to the forum.

Try this in G4:

=INDEX(E3:E9,MATCH(MAX(IF(A3:A9=F4,IF(B3:B9=G3,D3:D9*100+C3:C9))),IF(A3:A9=F4,IF(B3:B9=G3,D3:D9*100+C3:C9)),0))

confirmed with Control+Shift+Enter.

 

[martinli]

Thank you so much! This works great.

 

[Eric W]

You're welcome!

 

[Srinath]

Hello Eric,

Just curious to know, why do we need to multiply date by 100 ? and why is it not picking if we do not multiply, or else the formula works perfectly

 

[Eric W]

What we're doing here:

D3:D9*100+C3:C9

is multiplying the year by 100 and adding the month, for each row in the range. So a month of 7 and a year of 2018 gives us 201807, and a month of 3 and a year of 2019 gives us 201903. So when we compare 201807 and 201903, we can see that 201903 is more than 201807, so it comes last. If you create a whole list of those numbers, and use the MAX function, you'll get the largest date.

If you don't multiply by 100, you'll get 7 + 2018 = 2025 and 3 + 2019 = 2022, and if we compare 2025 and 2022, we'd get the wrong idea of which one comes first.