Sumifs for greatest date and other criteria

[martinli]

Hi,

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

A	B	C	D	E	F	G
Order	Rcvr-Order	Month	Year	Pct.
332673	901822653	7	2015	1.00		901822653
332673	901822653	5	2016	0.80	332673	1.84
332673	901822653	2	2018	0.04
332673	901822656	5	2016	0.04
332673	901822657	5	2016	0.04
332673	901822658	5	2016	0.04
332673	901822720	5	2016	0.04

<colgroup><col span="5"><col><col></colgroup><tbody>
</tbody>

<colgroup><col span="5"><col><col></colgroup><tbody></tbody>

<colgroup><col span="5"><col><col></colgroup><tbody></tbody>

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.