Finding the largest item within a period using an Array Formula

[nyx1234]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Item no.[/TD]
[TD]Catagory[/TD]
[TD]Date[/TD]
[TD]Amt[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]T01[/TD]
[TD]1/1/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]T01[/TD]
[TD]1/5/2017[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]T01[/TD]
[TD]5/5/2018[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]T01[/TD]
[TD]1/1/2017[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]T01[/TD]
[TD]1/1/2017[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]T02[/TD]
[TD]1/1/2017[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD].....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













I'm trying to determine the item no. under catagory "T01", within a period from 1/1/2017 to 1/31/2017 contributed highest amt, without using sum column or helper column. Is there a way to do this?

For example here, the sum of Item A under T01 within this period 25 and the sum of item B under T01 within this period is 30 - the highest based on the data provided is "item B"

I want to show the information in the list below, Red text are determined by formular.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Description
[/TD]
[TD]Catagory[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Item no.[/TD]
[TD]Amt[/TD]
[/TR]
[TR]
[TD]Top Item Jan17
[/TD]
[TD]T01[/TD]
[TD]1/1/2017[/TD]
[TD]1/31/2017[/TD]
[TD]B
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Second Item Jan17[/TD]
[TD]T01
[/TD]
[TD]1/1/2017[/TD]
[TD]1/31/2017[/TD]
[TD]A
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]Top item Jan17[/TD]
[TD]T01 and T01[/TD]
[TD]1/1/2017
[/TD]
[TD]1/31/2017[/TD]
[TD]B
[/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have researched online to find the answer - but it's quite difficult to apply the logic - I attached the links here -

Find Largest Sum and Item using an Array Formula:
https://www.mrexcel.com/forum/excel-questions/855461-find-largest-sum-item-using-array-formula.html

How to find the item with the largest sum in a table range?
https://www.extendoffice.com/documents/excel/4068-excel-find-largest-sum.html

Thank you for your kind attention!

 

[nyx1234]

I appreciate any advice, thank you!

 

[nyx1234]

My attempt was similar to Find Largest Sum and Item using an Array Formula:
https://www.mrexcel.com/forum/excel-...y-formula.html

But i didn't know how to add the date creteria into the formula.

Sumproduct instead of sumif...?

 

[Aladin Akyurek]

Given the type of your question, the following link would be more appropriate:
https://www.mrexcel.com/forum/excel-questions/915809-top-10-clients-without-using-pivot-table.html?highlight=jon999

Taking up your goal, that is, "I'm trying to determine the item no. under catagory "T01", within a period from 1/1/2017 to 1/31/2017 contributed highest amt, without using sum column or helper column", consider the following.

Book1
	A	B	C	D	E	F	G	H
1	Item no.	Catagory	Date	Amt		T01		highest amt
2	A	T01	1/1/2017	10		1/1/2017		30
3	A	T01	1/5/2017	15		1/31/2017		item
4	A	T01	5/5/2018	20				B
5	B	T01	1/1/2017	15				Q
6	B	T01	1/1/2017	15
7	B	T02	1/1/2017	15
8	Q	T01	1/12/2017	5
9	Q	T01	1/13/2017	25
Sheet1

In H2 control+shift+enter, not just enter:

=MAX(SUMIFS($D$2:$D$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$D$9)-ROW(INDEX($A$2:$D$9,1,1))+1),$A$2:$A$9),$B$2:$B$9,F1,$C$2:$C$9,">="&$F$2,$C$2:$C$9,"<="&$F$3))

In H4 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(SUMIFS($D$2:$D$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$D$9)-ROW(INDEX($A$2:$D$9,1,1))+1),$A$2:$A$9),$B$2:$B$9,$F$1,$C$2:$C$9,">="&$F$2,$C$2:$C$9,"<="&$F$3)=$H$2,ROW($A$2:$D$9)-ROW(INDEX($A$2:$D$9,1,1))+1),ROWS($H$4:H4))),"")

 

[nyx1234]

Aladin,

Thank you for your answer! Much Appreciate it! It works!

 

[Aladin Akyurek]

Aladin,

Thank you for your answer! Much Appreciate it! It works!

You are welcome. Thanks for the kind update.