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

 

[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 : A3)
....
....
....
C15 = B15 cell should not consider in max_range B2:B15 (that means, Maximum value should between (B2:B14) & Criteria range: A2:A15 , Criteria : A15)

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

Note : I slightly edited above thread. As there is no edit option , i again posted in reply

Regards
Raj Kumar

 

[Marcelo Branco]

Maybe...

Pasta1
	A	B	C
1	Category	Sales	Maximun
2	A	5200	4500
3	A	2300	5200
4	A	4500	5200
5	A	1700	5200
6	B	2600	9200
7	B	4000	9200
8	B	4000	9200
9	B	9200	4000
10	B	1800	9200
11	C	1300	8300
12	C	3400	8300
13	C	5300	8300
14	C	8300	5300
Plan1

Cell Formulas
Range	Formula
C2:C14	C2	=AGGREGATE(14,6,B$2:B$14/((A$2:A$14=A2)*(ROW(A$2:A$14)<>ROW(A2))),1)

I used AGGREGATE as a kind of MAXIFS

M.

 

[b_raj_kumar]

Hi Marcelo Branco

Thank you so much for reply. The solution is perfect.
I have one more doubt. I modified your formula to SUM. but it giving error. Can you please check?

SUM = AGGREGATE(9,6,$B$3:$B$15/(($A$3:$A$15=A3)*(ROW(A3:A15)<>ROW(A3))))

Regards
Raj Kumar

 

[Marcelo Branco]

I don't understand what you are trying to do.
The suggested formula gives the desired results. Why change it?
Why change the first parameter of the AGGREGATE function to 9 (SUM)? What do you want to add?

M.

 

[Peter_SSs]

For the original question, here is another way since you have MS 365.

22 06 16.xlsm
	A	B	C
1
2	A	5200	4500
3	A	2300	5200
4	A	4500	5200
5	A	1700	5200
6	B	2600	9200
7	B	4000	9200
8	B	4000	9200
9	B	9200	4000
10	B	1800	9200
11	C	1300	8300
12	C	3400	8300
13	C	5300	8300
14	C	8300	5300
Max

Cell Formulas
Range	Formula
C2:C14	C2	=LET(f,FILTER(B$2:B$14,A$2:A$14=A2),LARGE(f,1+(MAX(f)=B2)))

 

[b_raj_kumar]

I don't understand what you are trying to do.
The suggested formula gives the desired results. Why change it?
Why change the first parameter of the AGGREGATE function to 9 (SUM)? What do you want to add?

M.

HI Marcelo Branco ,
Your solution on "finding Maximum value" is perfect. There is another calculated column for SUM in actual summary. So, I tried to modify your solution in order to find Sum. But, the SUM formula returns #ERROR.

 

[Marcelo Branco]

There is another calculated column for SUM in actual summary. So, I tried to modify your solution in order to find Sum. But, the SUM formula returns #ERROR.

Could you, please, provide data along with expected results for this new column?

M.

 

[b_raj_kumar]

Could you, please, provide data along with expected results for this new column?

M.

Hi Marcelo Branco,


Please find the new scenario.

Logic : Calculated Column C3 = B3 cell should not consider in sum_range B3:B15 (that means, sum range should between (B4:B15) & Criteria range: A2:A15 , Criteria A3)
C4 = B4 cell should not consider in sum_range B3:B15 (that means, sum_range should between (B3,B5:B15) & Criteria range: A2:A15 , Criteria A4)
....
....
....
C15 = B15 cell should not consider in sum_range B3:B15 (that means, sum_range should between (B3:B14) & Criteria range: A2:A15 , Criteria A15)

 

[Marcelo Branco]

Try

Pasta1
	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
Plan1

Cell Formulas
Range	Formula
C3:C15	C3	=AGGREGATE(14,6,B$3:B$15/((A$3:A$15=A3)*(ROW(A$3:A$15)<>ROW(A3))),1)
D3:D15	D3	=SUMPRODUCT(--(A$3:A$15=A3),--(ROW(A$3:A$15)<>ROW(A3)),B$3:B$15)

M.