Find Most Recent Date with a positive cumulative sum based on criteria

[Don Swanson]

Hello,

I am struggling with a formula for work. I have an array with dates assigned to specific individuals. Several of these dates have positive and negative numbers associated with them. Also several of the dates are eligible and some are not. I want a formula that can return the most recent date assigned to a specific person that has a positive sum for all of the numbers associated with that date for that person. In my head I imagine the formula creates a dynamic array with eligible dates per person with the respective sums for each of those dates, then selects the most recent date in column E. I would be able to drag this formula down as my data set grows.

I arranged some data illustrate what I am looking for. Column E demonstrates what the formula would be doing.

John is the best example. He receives 1 point on 9/20, so on 9/21, Column E shows 9/20. However, by the time you get to E10. John has had both a negative and positive eligible point on 9/21, so the date with the most recent sum of positive points is 9/20/22.

Any help would be appreciated.

 

[iggydarsa]

If I understood your question correctly, you can get to the result by using a pivot table

On Pivot table
Filter: "Points for that date" and "Is Date Eligible"
Rows: "Name"
Values: "Date"*
Once you put the "Date" under "Value" section, click on the arrow next to Date and select Value Field Settings from the list.
On the new pop up select Max, then click on Number Format button and select a date format. Then click OK.

I hope that's what you wanted.

 

[Don Swanson]

Thank you so much for the reply. I am familiar with Pivot Table, but I am looking to use a formula in Column E that I can drag down as I add new data that will provide the answer. My thought it is that it would be a dynamic array formula such as Maxif(Date,Sumifs(person,dates,elligible)>0)

 

[Alex Blakenburg]

The below relies on your data being in ascending date order.
Is your "Is Date Eligible" column manually entered ? There are a number of rows that don't meet the criteria but are still flagged with Yes.
This is supported by the fact that you also use the description "Last eligible date returns negative sum"

My last column ignores that column and just uses your criteria as described by you.

20221207 Last Positive Result Don Swanson.xlsx
	A	B	C	D	E	F	G	H
1	Name	Date	Points for that date	Is Date Eligible		Most recent, previous, eligible date with positive sum:	Orig Most recent, previous, eligible date with positive sum:	Most recent, previous, eligible date with positive sum:
2	John	20/09/2022	1	Yes		No Previous eligible date	No Previous eligible date	No Previous eligible date
3	John	21/09/2022	1	Yes		20/09/2022	20/09/2022	20/09/2022
4	Jesse	21/09/2022	2	Yes		No Previous eligible date	No Previous eligible date	No Previous eligible date
5	Mark	21/09/2022	-1	Yes		No Previous eligible date	No Previous eligible date	No Previous eligible date
6	Phil	21/09/2022	2	no		No Previous eligible date	No Previous eligible date	No Previous eligible date
7	John	21/09/2022	-1	Yes		21/09/2022		21/09/2022
8	Jesse	21/09/2022	2	Yes		21/09/2022		21/09/2022
9	Mark	21/09/2022	1	Yes		Last eligible date returns negative sum		No Previous eligible date
10	Phil	21/09/2022	-1	Yes		No Previous eligible date	No Previous eligible date	21/09/2022
11	John	27/09/2022	1	Yes		Last eligible date returns negative sum		21/09/2022
12	Jesse	27/09/2022	-1	Yes		21/09/2022		21/09/2022
13	Mark	27/09/2022	2	no		21/09/2022		21/09/2022
14	Phil	27/09/2022	1	no		Last eligible date returns negative sum	Last eligible date returns negative sum	21/09/2022
15	John	27/09/2022	-1	Yes		27/09/2022		27/09/2022
16
17						Uses Yes/no flag	OPs expectations which look inconsistent with criteria	Uses base criteria and ignores Yes/no flag which looks wrong
Date

Cell Formulas
Range	Formula
F2:F15	F2	=LET(prevEligibleRow,XMATCH(1,($A$1:$A1=$A2)*($D$1:$D1="Yes"),0,-1), IFERROR( IF(INDEX($C$1:$C$15,prevEligibleRow)>0, INDEX($B$1:$B$15,prevEligibleRow), "Last eligible date returns negative sum"), "No Previous eligible date"))
H2:H15	H2	=IFERROR(INDEX($B$1:$B$15,XMATCH(1,($A$1:$A1=$A2)*($C$1:$C1>0),0,-1)), "No Previous eligible date")

 

[Don Swanson]

That worked great! Thank you so much!!!!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.