Value of Most Recant Order

[*shudder*]

Hi Guys,

I am trying to work out a formula for the value of the most recent customer order.

My initial though was to use SUMPRODUCT, however after looking myself I can't work out how to apply a condition to only sum the most recent date.

Example of data and expected results as follows:

Customer	Date	Product	Value
A	01/01/2023	Apples	100
B	01/01/2023	Pears	50
A	01/02/2023	Apples	100
A	01/02/2023	Oranges	75
C	01/03/2023	Oranges	20
C	01/03/2023	Apples	100
C	01/03/2023	Pears	200

A = 175
B = 50
C = 320

Thanks in advance.

 

[FormR]

Hi, perhaps some more varied example data would help a little to aid understanding, but maybe this..

Book1
	A	B	C	D
1	A	Jan 01 2023	Apples	100
2	B	Jan 01 2023	Pears	50
3	A	Jan 02 2023	Apples	100
4	A	Jan 02 2023	Oranges	75
5	C	Jan 03 2023	Oranges	20
6	C	Jan 03 2023	Apples	100
7	C	Jan 03 2023	Pears	200
8
9	A	175
10	B	50
11	C	320
Sheet1

Cell Formulas
Range	Formula
B9:B11	B9	=SUMIFS($D$1:$D$7,$A$1:$A$7,A9,$B$1:$B$7,MAXIFS($B$1:$B$7,$A$1:$A$7,A9))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[*shudder*]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff. I have updated my details. I am using office version 2016

 

[*shudder*]

Hi, perhaps some more varied example data would help a little to aid understanding, but maybe this..

Book1
	A	B	C	D
1	A	Jan 01 2023	Apples	100
2	B	Jan 01 2023	Pears	50
3	A	Jan 02 2023	Apples	100
4	A	Jan 02 2023	Oranges	75
5	C	Jan 03 2023	Oranges	20
6	C	Jan 03 2023	Apples	100
7	C	Jan 03 2023	Pears	200
8
9	A	175
10	B	50
11	C	320
Sheet1

Cell Formulas
Range	Formula
B9:B11	B9	=SUMIFS($D$1:$D$7,$A$1:$A$7,A9,$B$1:$B$7,MAXIFS($B$1:$B$7,$A$1:$A$7,A9))

Thanks, however when I try to replicate this in Excel it isn't returning any values. I have posted a simplified data set to get the general gist of what I am trying to achieve.

I am using 2016 version

 

[FormR]

when I try to replicate this in Excel it isn't returning any values.

I have posted a simplified data set

Was it perhaps too simplified then? How about something more representative and a description of what the actual ranges involved are?

 

[*shudder*]

I know it can be difficult implemented suggested formulas for particular data when simple examples are given. I did try copying the data and your formula example into a new SS and all of the results were returning 0,

My data is formatted as a table and the results are on a different sheet. Expanded data set as below:

Customer	Date	Product	Value
A	01/01/2023​	Apples	100​
A	01/02/2023​	Apples	100​
A	01/02/2023​	Oranges	75​
B	01/01/2023​	Pears	50​
C	01/01/2023​	Oranges	75​
C	01/01/2023​	Apples	75​
C	01/03/2023​	Pears	75​
C	01/03/2023​	Oranges	20​
C	01/03/2023​	Apples	100​
C	01/03/2023​	Pears	200​
D	01/01/2023​	Apples	100​
D	01/01/2023​	Pears	100​
E	01/01/2023​	Oranges	50​
E	01/01/2023​	Apples	75​
E	01/01/2023​	Oranges	200​
E	01/03/2023​	Pears	100​
E	01/04/2023​	Oranges	50​
E	01/05/2023​	Grapes	500​
E	01/05/2023​	Apples	100​
E	01/05/2023​	Pears	100​
E	01/05/2023​	Oranges	100​
E	01/05/2023​	Grapes	100​
F	01/01/2023​	Oranges	50​
F	01/01/2023​	Pears	50​
F	01/01/2023​	Grapes	50​
Expected Results
A	175​
B	50​
C	320​
D	200​
E	800​
F	150​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Customer	Date	Product	Value
2	A	01/01/2023	Apples	100			A	175
3	A	01/02/2023	Apples	100			B	50
4	A	01/02/2023	Oranges	75			C	395
5	B	01/01/2023	Pears	50			D	200
6	C	01/01/2023	Oranges	75			E	900
7	C	01/01/2023	Apples	75			F	150
8	C	01/03/2023	Pears	75
9	C	01/03/2023	Oranges	20
10	C	01/03/2023	Apples	100
11	C	01/03/2023	Pears	200
12	D	01/01/2023	Apples	100
13	D	01/01/2023	Pears	100
14	E	01/01/2023	Oranges	50
15	E	01/01/2023	Apples	75
16	E	01/01/2023	Oranges	200
17	E	01/03/2023	Pears	100
18	E	01/04/2023	Oranges	50
19	E	01/05/2023	Grapes	500
20	E	01/05/2023	Apples	100
21	E	01/05/2023	Pears	100
22	E	01/05/2023	Oranges	100
23	E	01/05/2023	Grapes	100
24	F	01/01/2023	Oranges	50
25	F	01/01/2023	Pears	50
26	F	01/01/2023	Grapes	50
27
Data

Cell Formulas
Range	Formula
H2:H7	H2	=SUMIFS($D$2:$D$100,$A$2:$A$100,G2,$B$2:$B$100,MAX(IF($A$2:$A$100=G2,$B$2:$B$100)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[*shudder*]

Thanks Fluff, that works

 

[Fluff]

Glad we could help & thanks for the feedback.