Using large function based on sumifs function

[jeosen]

Hi, I have a spreadsheet containing the following:

COLUMN A COLUMN B COLUMNC
ITEM SOLD DATE
APPLES 2 3/1/15
ORANGES 5 3/2/15
BANANA 6 3/2/15
APPLES 7 3/3/15
ORANGES 1 3/4/15
ORANGES 2 3/4/15

I want to have a top 2 list of which fruit sold the most based on cumulative total or sum of fruit sold based on column b for a particular date (column c).
is that possible?

[AZ-XL]

Hi

Used helper column
=SUMIF(A:A,A2,B:B)*(COUNTIF($A$2:A2,A2)=1)

all other in excel file

[jeosen]

Thanks for the reply.

Is it possible to do this without a helper column?

[AZ-XL]

Is it possible to do this without a helper column?

I dont think so (without using VBA)

[samba_ravi]

see the attached file

[benishiryo]

welcome to the forum, jeosen. say your date is in E1, try this array formula in E2:
=LARGE(IF($C$2:$C$10=$E$1,$B$2:$B$10),ROWS(E$2:E2))

and this formula in F2:
=INDEX($A$2:$A$10,LARGE(IF($C$2:$C$10=$E$1,ROW($B$2:$B$10)),ROWS(E$2:E2))-ROW($B$2)+1)

...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.

Data Range

	A	B	C	D	E	F
1	ITEM	SOLD	DATE		4/3/2015
2	APPLES	2	1/3/2015		2	ORANGES
3	ORANGES	5	2/3/2015		1	ORANGES
4	BANANA	6	2/3/2015
5	APPLES	7	3/3/2015
6	ORANGES	1	4/3/2015
7	ORANGES	2	4/3/2015