Last occurrence respective to current Cell

Eric W

MrExcel MVP

Joined

Aug 18, 2015

Messages

13,034

• May 18, 2024

• #2

I can't vouch for Google Sheets, but this works in Excel:

Book1
	A	B	C	D	E	F
1	Date	Means		Elapsed Time	Elapsed Time From Last	SHOULD BE
2	2/10/2023	A
3	2/19/2023	T		9
4	2/25/2023	T		6		6
5	3/12/2023	H		15
6	3/21/2023	T		9		24
7	3/27/2023	A		6		45
8	3/30/2023	A		3		3
9	4/5/2023	A		6		6
10	4/11/2023	A		6		6
11	4/14/2023	H		3		33
12	4/23/2023	B		9
13	4/28/2023	T		5		38
14	4/30/2023	A		2		19
15	5/7/2023	A		7		7
16	5/11/2023	A		4		4
17	5/14/2023	A		3		3
18	5/19/2023	A		5		5
19	5/24/2023	T		5		26
20	5/29/2023	A		5		10
21	6/3/2023	B		5		41
22	6/9/2023	A		6		11
23	6/15/2023	A		6		6
24	6/19/2023	A		4		4
25	6/23/2023	A		4		4
Sheet5

Cell Formulas
Range	Formula
D3:D25	D3	=A3-A2
F3:F25	F3	=IFERROR(SUM(INDEX(D$3:D3,XMATCH(B3,B$2:B2,0,-1)):D3),"")

 

Upvote 0

Solution

I

ItalianPlatinum

Well-known Member

Joined

Mar 23, 2017

Messages

803

Office Version

1. 365
2. 2019

Platform

1. Windows

• May 18, 2024

• #3

Is that an Excel 365 only formula? currently using my 2019 version and kicks back blanks in excel. But it is actually working in google sheets. I in the meantime also came up with this they seems to both be yielding same results. Which is promising so thank you!

=A3-IF(MAXIFS($A$2:$A2,$B$2:$B2,B3)=0,A3,MAXIFS($A$2:$A2,$B$2:$B2,B3))

 

Upvote 0

Eric W

MrExcel MVP

Joined

Aug 18, 2015

Messages

13,034

• May 19, 2024

• #4

XMATCH is only in Excel 2021 and newer. But after looking at your formula, I came up with another version that doesn't use XMATCH:

=IFERROR(A3-1/(1/MAXIFS(A$2:A2,B$2:B2,B3)),"")

 

Upvote 0

I

ItalianPlatinum

Well-known Member

Joined

Mar 23, 2017

Messages

803

Office Version

1. 365
2. 2019

Platform

1. Windows

• May 19, 2024

• #5

It works in google sheets so its fine. there is no drawback using XMATCH is there?

 

Upvote 0

Eric W

MrExcel MVP

Joined

Aug 18, 2015

Messages

13,034

• May 19, 2024

• #6

No drawback, except it doesn't work in Excel 2019.

 

Upvote 0