Finding the largest item within a period using an Array Formula

[nyx1234]

Item no.	Catagory	Date	Amt
A	T01	1/1/2017	10
A	T01	1/5/2017	15
A	T01	5/5/2018	20
B	T01	1/1/2017	15
B	T01	1/1/2017	15
B	T02	1/1/2017	15
.....

<tbody>
</tbody>

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.

Description	Catagory	Start Date	End Date	Item no.	Amt
Top Item Jan17	T01	1/1/2017	1/31/2017	B	30
Second Item Jan17	T01	1/1/2017	1/31/2017	A	25
Top item Jan17	T01 and T01	1/1/2017	1/31/2017	B	45

<tbody>
</tbody>

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.