Find maximum value from range based on (complex) criteria

[b_raj_kumar]

Hi

I want to find Maximum value from range using "MAXIFS" (max_Range: B column , Criteria Range- A column, Criteria : A2). In that range, Current category sale value should not consider.

Logic : Calculated Column C2 = B2 cell should not consider in max_range B2:B15 (that means, Maximum value should between (B3:B15) & Criteria range: A2:A15 , Criteria A2)
C3 = B3 cell should not consider in max_range B2:B15 (that means, Maximum value should between (B2,B4:B15) & Criteria range: A2:A15 , Criteria A2)
....
....
....
C15 = B15 cell should not consider in max_range B2:B15 (that means, Maximum value should between (B2:B14) & Criteria range: A2:A15 , Criteria A2)

Could anyone please help me out? Thank you in advance.

Regards
Raj Kumar

 

[Peter_SSs]

.. or

22 06 17.xlsm
	A	B	C	D
1
2	Category	Sales	Maximun	Sum
3	A	5200	4500	8500
4	A	2300	5200	11400
5	A	4500	5200	9200
6	A	1700	5200	12000
7	B	2600	9200	19000
8	B	4000	9200	17600
9	B	4000	9200	17600
10	B	9200	4000	12400
11	B	1800	9200	19800
12	C	1300	8300	17000
13	C	3400	8300	14900
14	C	5300	8300	13000
15	C	8300	5300	10000
Max & Sum

Cell Formulas
Range	Formula
C3:C15	C3	=LET(f,FILTER(B$3:B$15,A$3:A$15=A3),LARGE(f,1+(MAX(f)=B3)))
D3:D15	D3	=SUMIF(A$3:A$15,A3,B$3:B$15)-B3