SUMIF last five date for a particular product

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
199
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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))))
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
Works perfectly thank you Aladin, sorry again for the mix up and changing the requirements. I appreciate your help. Thank you John
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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