Sumproduct, row, max, match - help!

[traugotts]

Ok, I'm not exactly sure what formula I need... I want to return the value from Column C that is the last entry for a specific item in column A.

The data shows how many items were taken (in column B) and how many are remaining (in Column C). For example if I had:
[Column A] [Column B] [Column C]
banana -2 5
apple -3 6
banana -1 4
orange -1 5
orange -2 3
apple -2 4


I want the formula to return these results (on a separate sheet):
Apples 4
Oranges 3
Bananas 4

[traugotts]

oops, ok the data in the columns is respective as follows: fruit, negative amount, remaining positive amount

( Sorry, the formatting got messed up. )

[FDibbins]

Assuming the values in C will always be decreasing (although I have a feeling at some point they will climb again), try this ARRAY formula...

	A	B	C	D	E	F
1	Fruit	Amt	Balance
2	banana	-2	5		Apple	4
3	apple	-3	6		Orange	3
4	banana	-1	4		banana	4
5	orange	-1	5
6	orange	-2	3
7	apple	-2	4

F2=MIN(IF($A$2:$A$7=E2,$C$2:$C$7))
copied down
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[onroute]

Try if this works, the 1/(xxx) basically reverse your table before the lookup does its stuff.

I'm using Ford's table format.

Please Login or Register  to view this content.