Returning most recent billed amount great than zero.

[DrTheoRio]

I'm trying to come up with a formula that will return the most recent billed amount for a customer that is greater than zero. From the example data in the attached screenshot, a formula for column D that would result in 120 being returned for cells D5 & D6, and 12 for cell D11. Any help would be greatly appreciated.

 

[etaf]

your profile says 2016 version, so maxifs() wont be available

=MAX(IF($A$2:$A$1000=A2,$C$2:$C$1000))
use control+shift+enter to get {}

you say greater than zero - if only zero in the list - what do you want to report
If negative only what do you want to report

if you have 2019 version or later then
MAXIFS()

Book11
	A	B	C	D
1
2	a		1	11
3	a		2	11
4	b		3	12
5	b		4	12
6	b		5	12
7	c		6	8
8	c		7	8
9	c		8	8
10	d		9	10
11	d		10	10
12	a		11	11
13	b		12	12
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=MAX(IF($A$2:$A$1000=A2,$C$2:$C$1000))

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future , I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Perhaps this?
It assumes that the dates for each customer are in ascending order like your samples.

24 03 01.xlsm
	A	B	C	D
2	73	Aug-21	45	45
3	73	Sep-21	80	80
4	73	Oct-21	120	120
5	73	Nov-21	0	120
6	73	Dec-21	0	120
7	80	Aug-21	55	55
8	80	Sep-21	99	99
9	80	Oct-21	56	56
10	80	Nov-21	12	12
11	80	Dec-21	0	12
12	31	Aug-21	55	55
13	31	Sep-21	65	65
14	31	Oct-21	120	120
15	31	Nov-21	40	40
16	31	Dec-21	35	35
Last billed

Cell Formulas
Range	Formula
D2:D16	D2	=INDEX(C:C,AGGREGATE(14,6,ROW(C$2:C2)/((A$2:A2=A2)*(C$2:C2>0)),1))

Or if a customer might have nothing billed in the first month (or more) try something like this.

24 03 01.xlsm
	A	B	C	D
2	73	Aug-21	0
3	73	Sep-21	80	80
4	73	Oct-21	120	120
5	73	Nov-21	0	120
6	73	Dec-21	0	120
7	80	Aug-21	0
8	80	Sep-21	0
9	80	Oct-21	56	56
10	80	Nov-21	12	12
11	80	Dec-21	0	12
12	31	Aug-21	55	55
13	31	Sep-21	65	65
14	31	Oct-21	120	120
15	31	Nov-21	40	40
16	31	Dec-21	35	35
Last billed (2)

Cell Formulas
Range	Formula
D2:D16	D2	=IFERROR(INDEX(C:C,AGGREGATE(14,6,ROW(C$2:C2)/((A$2:A2=A2)*(C$2:C2>0)),1)),"")

 

[DrTheoRio]

Thank you for all of your help, I greatly appreciate it. I continue to be amazed by the power of excel and the knowledge base here. All the best to you and I will figure out how to use mini-sheets.
-T

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.