Find multiple last entries in a dataset

Jockeyp

New Member
Joined
Apr 24, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. MacOS
Hi, have checked other related posts and not found anything that can help me yet.

I have a list of data with 3 columns; date, fruit and amount sold. The list is added to every day with new data.

I would like a formula to find the last 4 entries of a fruit (i.e Banana) from the most recent date and then produce the sum of the amount of bananas sold on those 4 identified days. When the data is updated, I would like the formula to also update to include the latest additions in finding the last 4 entries.

I appreciate that if I put my data into an Excel table and have the formulas search that, it will dynamically update when I add new data. My problem is finding a formula that searches the whole data set but only finds the last 4 entries.

I have attached an image of the excel layout. Im looking for a formula if F3. In the image, the answer for bananas for the last 4 entries should sum up to 16.

Any help would be greatly appreciated.
 

Attachments

  • Screenshot 2021-04-24 at 09.05.13.png
    Screenshot 2021-04-24 at 09.05.13.png
    162.3 KB · Views: 9

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,679
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
Based This Thread

Sum the last n values in a row based on a criteria.

Answer is:
Book1.xlsm
ABCDEFGH
1DateFruitsTotal Sold
21/1/2021Bananas2FruitsTotal Last 4 Entries
31/2/2021Bananas2Bananas12
41/3/2021Apples4Apples11
51/4/2021Pears5Pears11
61/5/2021Apples3
71/6/2021Apples2
81/7/2021Pears2
91/8/2021Bananas5
101/9/2021Pears3
111/10/2021Pears4
121/11/2021Bananas3
131/12/2021Bananas4
141/13/2021Apples3
151/14/2021Pears5
161/15/2021Apples5
171/16/2021Apples3
181/17/2021Pears4
191/18/2021Bananas5
201/19/2021Pears5
211/20/2021Pears4
221/21/2021Bananas2
231/22/2021Bananas2
241/23/2021Apples4
251/24/2021Pears3
261/25/2021Apples4
271/26/2021Apples3
281/27/2021Pears4
291/28/2021Bananas2
301/29/2021Pears2
311/30/2021Pears3
321/31/2021Bananas4
332/1/2021Bananas4
342/2/2021Apples2
352/3/2021Pears4
362/4/2021Apples3
372/5/2021Apples3
382/6/2021Pears2
39
Sheet2
Cell Formulas
RangeFormula
F3:F5F3=SUMPRODUCT(($B$2:$B$38=E3)*(ROW(2:38)=LARGE(($B$2:$B$38=E3)*ROW(2:38),{1,2,3,4}))*$C$2:$C$38)
 
Solution

Jockeyp

New Member
Joined
Apr 24, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. MacOS
Thanks so much for this - it worked! Can you talk me through the formula please? I would like to learn how the logic is working.

I can see that the overall formula is a sum product of the fruit name and the amounts in column C. Is the LARGE in relation to the dates to find the most recent one?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,679
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Not Date, this formula working based Row number of Data. If you want to do based dates change row section to column A range.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,679
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
this is formula based Column A values
Excel Formula:
=SUMPRODUCT(($B$2:$B$38=E3)*($A$2:$A$38=LARGE(($B$2:$B$38=E3)*$A$2:$A$38,{1,2,3,4}))*$C$2:$C$38)
 

Rahul1987

Board Regular
Joined
Apr 10, 2021
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
Hey , Sorry for the late post

How about

Book1
ABCDEFGH
1DateFruitsTotal SoldLast 4
21-Jan-21Bananas2FruitsTotal Last 4 Entries4
32-Jan-21Bananas2Bananas12
43-Jan-21Apples4Apples11
54-Jan-21Pears5Pears11
65-Jan-21Apples3
76-Jan-21Apples2
87-Jan-21Pears2
98-Jan-21Bananas5
109-Jan-21Pears3
1110-Jan-21Pears4
1211-Jan-21Bananas3
1312-Jan-21Bananas4
1413-Jan-21Apples3
1514-Jan-21Pears5
1615-Jan-21Apples5
1716-Jan-21Apples3
1817-Jan-21Pears4
1918-Jan-21Bananas5
2019-Jan-21Pears5
2120-Jan-21Pears4
2221-Jan-21Bananas2
2322-Jan-21Bananas2
2423-Jan-21Apples4
2524-Jan-21Pears3
2625-Jan-21Apples4
2726-Jan-21Apples3
2827-Jan-21Pears4
2928-Jan-21Bananas2
3029-Jan-21Pears2
3130-Jan-21Pears3
3231-Jan-21Bananas4
331-Feb-21Bananas4
342-Feb-21Apples2
353-Feb-21Pears4
364-Feb-21Apples3
375-Feb-21Apples3
386-Feb-21Pears2
Sheet1
Cell Formulas
RangeFormula
F3:F5F3=SUM(IF($B$2:$B$38=E3,IF(ROW($A$2:$A$38)>=LARGE(IF($B$2:$B$38=E3,ROW($A$2:$A$38)),$H$2),$C$2:$C$38)))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Forum statistics

Threads
1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

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