Find Last Date Where Amount > 0

[masouder]

I am trying to calculate the last date for a designated account from a table where the net Amount for that account on that date is greater than 0 (in other words, where there was no return). I'm pretty sure this can be solved with an array formula (or SUMPRODUCT?), but I cannot figure out how to do it. Please see below.

In the below example I am trying to identify the last date for Account 1. Because the net amount for 08/03/2022 is 0 the formula should return 08/02/2022. I can write the array formula to calculate the last date (see cell G7 with formula displayed in H7), but I cannot figure out how to add a component to the formula that excludes dates where the net amount is 0. Can anyone help with a solution?

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3
4		Date	Account	Amount
5		8/1/2022	1	10
6		8/2/2022	1	10		Account	1
7		8/3/2022	1	10		Last Date	8/3/2022	=MAX(IF((C5:C12=G6),B5:B12))
8		8/3/2022	1	-10
9		8/1/2022	2	5
10		8/2/2022	2	5
11		8/2/2022	2	-5
12		8/4/2022	2	5
13
14
Sheet1

Cell Formulas
Range	Formula
G7	G7	=MAX(IF((C5:C12=G6),B5:B12))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you!

 

[jasonb75]

Is your profile showing the correct version of Excel?

I haven't tried anything yet, but I suspect that this would be much more difficult to achieve with Excel 2013 than with Office 365.

 

[masouder]

Is your profile showing the correct version of Excel?

I haven't tried anything yet, but I suspect that this would be much more difficult to achieve with Excel 2013 than with Office 365.

Yes, Excel 2013.

 

[jasonb75]

I was just trying a couple of things whilst waiting for you to reply and think I may have cracked it a little easier than I first thought possible. I've only done a quick test with your sample data but can see no reason for this to fail with different data.
This will need to be array confirmed in Excel 2013.

=MAX(IF(SUMIFS(D5:D12,C5:C12,C5:C12,B5:B12,B5:B12)>0,IF(C5:C12=G6,B5:B12)))

 

[masouder]

Thank you, that appears to be it. I was close, but couldn't quite get it.

Curious, you mentioned an easier solution in 365. Can you share what solution is?

 

[jasonb75]

Curious, you mentioned an easier solution in 365.

When I said that, I was expecting the formula for 2013 to be much more complicated than it turned out to be. I think that the current formula will be as good as it gets.

 

[masouder]

When I said that, I was expecting the formula for 2013 to be much more complicated than it turned out to be. I think that the current formula will be as good as it gets.

Thank again.

One last question. I attempted to rewrite the formulas using the AND function to reduce the two IF functions to one, but it does not work (returns 0). I did remember to enter it as an array formula. Any idea why it does not work?

=MAX(IF(AND(C5:C12=G6,SUMIFS(D5:D12,C5:C12,C5:C12,B5:B12,B5:B12)>0),B5:B12))

 

[jasonb75]

Using 2 IF's enables us to separate the TRUE and FALSE results from the array (only the TRUE ones are useful). As soon as you thrown AND into it the hierarchy changes and a single FALSE result takes priority over any TRUE results, wiping out the useful data that the 2 IF's can separate.