SUMIF last five date for a particular product

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198
Hello,

I am struggling to think how I can do this with a SUMIFS or SUMPRODUCT function.

I have a list of Products and their Sale Date and Sale Amounts. What I would like to do is a SUM of all the Sale Amount for each Product, but only the last five Sale (using the Sale Date) I could get the MAX Sale Date but not sure to the get the 5th Sale Date for the range.

Thank you for your help.

John
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Row\Col
A​
B​
C​
F​
G​
1​
proddateamount
2​
x
8/3/2016​
10​
a
105
3​
8/22/2016​
14​
4​
a
9/13/2016​
35​
5​
b
9/27/2016​
20​
6​
a
10/2/2016​
30​
7​
c
10/8/2016​
12​
8​
c
10/21/2016​
25​
9​
b
10/23/2016​
20​
10​
x
11/1/2016​
15​
11​
a
30​
12​
a
12/2/2016​
20​
13​
b
12/10/2016​
15​
14​
a
12/18/2016​
20​

In G2 control+shift+enter, not just enter:

=SUM(IF($A$2:$A$14=$F2,IF(ISNUMBER($B$2:$B$14),IF($B$2:$B$14>=LARGE(IF($A$2:$A$14=$F2,$B$2:$B$14),MIN(5,COUNTIFS($A$2:$A$14,$F2))),$C$2:$C$14))))
 

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198
Hello Aladin,

Thank you for your reply. I have changed your formula slightly because instead of the SUM I wanted the return the 5 date value.

Code:
=MIN(IF(Data!$D$3:$E$4403=$B1,IF(ISNUMBER(Data!$C$3:$C$4403),IF(Data!$C$3:$C$4403>=LARGE(IF(Data!$D$3:$D$4403=$B1,Data!$C$3:$C$4403),MIN(5,COUNTIFS(Data!$D$3:$E$4403,$B1))),Data!$C$3:$C$4403))))

This does work but I have tried to modify it slightly. I actually have two Product columns D and E in my example above. I tried to extend the range but it does not take into account the 2nd column. Is there a way to get this to work. So I have two product column which can be the same product (just depending on the category) and a date field. I would like to find the 5 latest sale date for a product name that could be in either columns D or E

Thank you for your help

John
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
You wrote: "What I would like to do is a SUM of all the Sale Amount for each Product, but only the last five Sale (using the Sale Date)..."

If this was not your intent, try to say what value/result you want given the sample I set up...
 

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198

ADVERTISEMENT

I know what I have written, however I didn't notice that their was two columns the product name could be in, Apologies!

In your example there would be another column between A and B with the Product type again. So in column A would be the Level 1 view of product (a) in your example but you might also see Product (a) in the new column B Level 2. If it is in either A or B then return the 5th Sale Date.

Hope this makes it more clearer.

Thank you again,

John
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I know what I have written, however I didn't notice that their was two columns the product name could be in, Apologies!

In your example there would be another column between A and B with the Product type again. So in column A would be the Level 1 view of product (a) in your example but you might also see Product (a) in the new column B Level 2. If it is in either A or B then return the 5th Sale Date.

Hope this makes it more clearer.

Thank you again,

John

Thus not a sum of amounts, just the 5th largest sales date of a specified product?
 

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198

ADVERTISEMENT

Yeas but the product spans across two columns
 

Guru008

Board Regular
Joined
Dec 19, 2016
Messages
126
Hi,

Try this
=SUM(OFFSET($B$1,COUNT(B:B)-5,0,5))

I am able to respond in real time to you until it is solved!
 

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198
Works perfectly thank you Aladin, sorry again for the mix up and changing the requirements. I appreciate your help. Thank you John
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,748
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top