how to find the MAX month based on 2 criteria

[slapman]

hello all.

i have sales data with the following:
A B C D
Month-year | Part number | SALE QTY 2020 | SALE QTY 2019

02/2020 XXXX 2
.....
...
... XXXX 5
... ZZZZ 6
09/2019 YYYY 9
.....
...
... XXXX 12
... ZZZZ 6

in order to project the purchuses from the supplier im trying to i dentify the "PEAK" month of sales per year 2019 / 2020

the outcome should tell me what is the MONTH (not the value of QTY) which the peak occured.

>>> I have excel 2013 (maxIFS not availabe).

thank you very much.

eddie.

 

[slapman]

sorry, the exaple turned bad in last post
uploading the example here:
in the PEAK column i need the answer per line, so the answer may duplicate , which is ok for me.

MONTH​	PN​	SALE QTY 2019​	SALE QTY 2020​	PEAK MONTH 2019​	PEAK MONTH 2020​
02/2019​	51601370200​	1​		??​	??​
04/2019​	51603502720​	4​		??​	??​
05/2019​	51601370200​	12​		??​	??​
08/2019​	51603502610​	2​		??​	??​
08/2019​	51603500350​	4​		??​	??​
09/2019​	51603500350​	9​		??​	??​
09/2019​	51603502720​	18​		??​	??​
01/2020​	51601370200​		0​	??​	??​
01/2020​	51603502610​		1​	??​	??​
02/2020​	51603500350​		1​	??​	??​

​

​

​

​

​

 

[slapman]

anyone?

 

[slapman]

hello all.

i have sales data with the following:

MONTH	PN	SALE QTY 2019	SALE QTY 2020	PEAK MONTH 2019	PEAK MONTH 2020
02/2019	51601370200	1		??	??
04/2019	51603502720	4		??	??
05/2019	51601370200	12		??	??
08/2019	51603502610	2		??	??
08/2019	51603500350	4		??	??
09/2019	51603500350	9		??	??
09/2019	51603502720	18		??	??
01/2020	51601370200		0	??	??
01/2020	51603502610		1	??	??
02/2020	51603500350		1	??	??

n order to project the purchases from the supplier im trying to i identify the "PEAK" month of sales per year 2019 / 2020

the outcome should tell me what is the MONTH (not the value of QTY) which the peak occurred.

>>> I have excel 2013 (maxIFS not available).

thank you very much.

eddie.

 

[Peter_SSs]

Welcome to the MrExcel board!

Always a good idea to include the expected results so we know what we are aiming for, For example, I'm unclear what result(s) you expect for 2020 where two months have equal top sales.

To get the first month with max sales, try these, copied down.

20 03 08.xlsm
	A	B	C	D	E	F
1	MONTH	PN	SALE QTY 2019	SALE QTY 2020	PEAK MONTH 2019	PEAK MONTH 2020
2	Feb-19	51601370200	1		Sep-19	Jan-20
3	Apr-19	51603502720	4		Sep-19	Jan-20
4	May-19	51601370200	12		Sep-19	Jan-20
5	Aug-19	51603502610	2		Sep-19	Jan-20
6	Aug-19	51603500350	4		Sep-19	Jan-20
7	Sep-19	51603500350	9		Sep-19	Jan-20
8	Sep-19	51603502720	18		Sep-19	Jan-20
9	Jan-20	51601370200		0	Sep-19	Jan-20
10	Jan-20	51603502610		1	Sep-19	Jan-20
11	Feb-20	51603500350		1	Sep-19	Jan-20
Peak

Cell Formulas
Range	Formula
E2:F11	E2	=INDEX($A$2:$A$11,MATCH(MAX(C$2:C$11),C$2:C$11,0))

 

[slapman]

Welcome to the MrExcel board!

Always a good idea to include the expected results so we know what we are aiming for, For example, I'm unclear what result(s) you expect for 2020 where two months have equal top sales.

To get the first month with max sales, try these, copied down.

20 03 08.xlsm
	A	B	C	D	E	F
1	MONTH	PN	SALE QTY 2019	SALE QTY 2020	PEAK MONTH 2019	PEAK MONTH 2020
2	Feb-19	51601370200	1		Sep-19	Jan-20
3	Apr-19	51603502720	4		Sep-19	Jan-20
4	May-19	51601370200	12		Sep-19	Jan-20
5	Aug-19	51603502610	2		Sep-19	Jan-20
6	Aug-19	51603500350	4		Sep-19	Jan-20
7	Sep-19	51603500350	9		Sep-19	Jan-20
8	Sep-19	51603502720	18		Sep-19	Jan-20
9	Jan-20	51601370200		0	Sep-19	Jan-20
10	Jan-20	51603502610		1	Sep-19	Jan-20
11	Feb-20	51603500350		1	Sep-19	Jan-20
Peak

Cell Formulas
Range	Formula
E2:F11	E2	=INDEX($A$2:$A$11,MATCH(MAX(C$2:C$11),C$2:C$11,0))

thank you for your reply.

i just painted a picture , but basically there is no way in the world that two (or more) months will come out with the same results so i'm expecting to have an absolute 1 value to come out.

 

[slapman]

Welcome to the MrExcel board!

Always a good idea to include the expected results so we know what we are aiming for, For example, I'm unclear what result(s) you expect for 2020 where two months have equal top sales.

To get the first month with max sales, try these, copied down.

Im sorry, i dont know how to edit my posts , so i have to open new post (please guide me if possible).
the formula you presented gives wrong result.

for 51601370200 result came sep-19
but in fact peak sales for that PN was in May-19.

the idea is to show the max sales month per PN by month (each year in separate)

thanks again.

 

[Peter_SSs]

the idea is to show the max sales month per PN by month (each year in separate)

That part wan't mentioned originally.

im trying to i dentify the "PEAK" month of sales per year 2019 / 2020

the outcome should tell me what is the MONTH (not the value of QTY) which the peak occured.

If this is not what you want, please post sample data and all expected results with explanation in relation to that sample data.

20 03 08.xlsm
	A	B	C	D	E	F
1	MONTH	PN	SALE QTY 2019	SALE QTY 2020	PEAK MONTH 2019	PEAK MONTH 2020
2	Feb-19	51601370200	1		May-19
3	Apr-19	51603502720	4		Sep-19
4	May-19	51601370200	12		May-19
5	Aug-19	51603502610	2		Aug-19
6	Aug-19	51603500350	4		Sep-19
7	Sep-19	51603500350	9		Sep-19
8	Sep-19	51603502720	18		Sep-19
9	Jan-20	51601370200		0		Jan-20
10	Jan-20	51603502610		1		Jan-20
11	Feb-20	51603500350		1		Feb-20
Peak

Cell Formulas
Range	Formula
E2:E11	E2	=IF(C2="","",AGGREGATE(14,6,A$2:A$11/((B$2:B$11=B2)*(C$2:C$11=AGGREGATE(14,6,C$2:C$11/(B$2:B$11=B2),1))),1))
F2:F11	F2	=IF(D2="","",AGGREGATE(14,6,A$2:A$11/((B$2:B$11=B2)*(D$2:D$11=AGGREGATE(14,6,D$2:D$11/(B$2:B$11=B2),1))),1))

 

[slapman]

That part wan't mentioned originally.
If this is not what you want, please post sample data and all expected results with explanation in relation to that sample data.

OMG!
You nailed it with an outer-space formula!
it worked and you have no idea how helpful it is to me.

1000s of thanks to you!

 

[Peter_SSs]

OMG!
You nailed it with an outer-space formula!
it worked and you have no idea how helpful it is to me.

1000s of thanks to you!

You're welcome. Thanks for the enthusiastic follow-up.