Find the last data in a table with condition

[harimin]

I have a table consisting of 5 columns: time, customer, status, amount, and end balance.
I'd like to create a report that shows: name of customer and end balance.
Conditions: only show the end balance where the status is "success".

Please help, thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

You cannot display images stored on your own system. In any case, we wouldn't be able to copy from an image to test our suggestions so you are better to follow the info provided through my signature block below to post a small screen shot here that can be copied.

In any case, see if this helps.
I've assumed that those 5 columns are columns A:E and that you have a customer name in cell G2.
I've also assumed that a particular customer only has at most one row that says "success" or that the time values in column A for any customer increase as you move down the sheet.

In cell H2 I have the following formula. This is an array formula, so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

{=INDEX($E$2:$E$20,MAX(IF($B$2:$B$20=G2,IF($C$2:$C$20="success",ROW($E$2:$E$20)-ROW($E$2)+1))))}

Adjust the "$20" values in my formula to be at least as large as the last row in your data.

 

[Steve_R]

I did not see your image but think my test data is roughly what you have.

Pivot tables are great for summarising. To create one that I suspect will do what you need:

-Click Insert
-Pivot table
-Select your data table (if you have it named use the range name)
-Choose between a cell away from data in existing sheet or new sheet
-Click OK

-From the field list, drag customer to the Report Filter area
-Drag Customer onto the rown lable area
-Drag end balance to the Value area
-Check that the Label in the value area starts with 'Sum of...'
If not, click that field; click Value Field Setting; choose Sum; click OK

** Missed a step below, while you have the Value Field Setting open, Change the format to currency.

-Now on the pivot table created on your sheet, use the dropdown to change Status to success.

** Also in Pivot table, you can change 'Row Labels' to Customers and 'Sum of End Balance' to something else if you wish
NOTES
Pivot tables do not referesh automatically when you change data
Pivot tables do not automatically resize to fit data when you add more redords


My test data

Time	Customer	Status	End Balance
12:55:46 AM	D	Fail	$226.00
1:21:21 AM	B	Fail	$184.00
1:31:40 AM	A	Success	$170.00
3:15:41 AM	D	Success	$188.00
7:42:55 AM	A	Success	$226.00
9:22:03 AM	A	Success	$236.00
12:10:56 PM	D	Success	$203.00
12:51:37 PM	B	Success	$255.00
2:25:38 PM	E	Success	$150.00
3:46:10 PM	D	Success	$275.00
6:15:20 PM	C	Success	$232.00
8:20:46 PM	C	Fail	$162.00
9:34:38 PM	D	Success	$295.00

<tbody>
</tbody>

(Customers were A,B,C...)

My result

(Success is a drop-down selection)

Status	Success
Row Labels	Sum of End Balance
A	632
B	255
C	232
D	961
E	150
Grand Total	2230

<tbody>
</tbody>