I want The lowest (minimum) and the highest (maximum) amount of total sales with criteria

[Rsdg]

hello guys , i have a data that include this columns , and i want to know the lowest and highest sale for city 1 in these 5 month , that's mean formula must sum the sale of city 1 for 5 month Individually and show the lowest sale

Book1
	A	B	C	D	E	F	G	H
1	month	city code	sale			city code	The lowest amount of total sales in these 5 months (minimum)	The highest amount of total sales in these 5 months (maximum)
2	2021-01	1	15,526,870			1
3	2021-01	1	21,578,633			2
4	2021-01	1	3,337,780			3
5	2021-01	2	1,897,163
6	2021-01	3	14,323,674
7	2021-01	3	15,268,137
8	2021-02	1	54,982,451
9	2021-02	1	12,425,854
10	2021-02	2	12,432,354
11	2021-02	2	7,812,365
12	2021-03	2	951,327
13	2021-03	2	1,500,369
14	2021-03	3	1,946,358
15	2021-03	3	21,499,128
16	2021-03	3	4,551,746
17	2021-03	3	35,896,412
18	2021-04	1	4,000,365
19	2021-04	1	1,500,000
20	2021-04	1	2,500,005
21	2021-04	1	3,125,844
22	2021-05	1	1,000,000
23	2021-05	1	2,000,000
24	2021-05	2	5,648,565
25	2021-05	2	3,669,540
26	2021-05	2	15,268,700
27	2021-05	2	15,264,471
28	2021-05	2	13,543,658
29	2021-05	3	13,131,239
30	2021-05	3	25,688,845
Sheet1

 

[6StringJazzer]

This requires an array formula for Excel 2016. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

$scratch.xlsm
	F	G	H
1	city code	The lowest amount of total sales in these 5 months (minimum)	The highest amount of total sales in these 5 months (maximum)
2	1	1,000,000	54,982,451
3	2	951,327	15,268,700
4	3	1,946,358	35,896,412
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=MIN(IF($B$2:$B$30=$F2,$C$2:$C$30,MAX($C:$C)))
H2:H4	H2	=MAX(IF($B$2:$B$30=$F2,$C$2:$C$30,0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Rsdg]

This requires an array formula for Excel 2016. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

$scratch.xlsm
	F	G	H
1	city code	The lowest amount of total sales in these 5 months (minimum)	The highest amount of total sales in these 5 months (maximum)
2	1	1,000,000	54,982,451
3	2	951,327	15,268,700
4	3	1,946,358	35,896,412
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=MIN(IF($B$2:$B$30=$F2,$C$2:$C$30,MAX($C:$C)))
H2:H4	H2	=MAX(IF($B$2:$B$30=$F2,$C$2:$C$30,0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

thanks , but i think i did not explain well , this formula shows me the lowest value in city 1 , but i want the lowest value between months for city 1 , for example your formula shows 1.000.000 but in 2022.05 total sale for city 1 is 3.000.000 That should Comparison with another months for city 1 , thanks again

 

[6StringJazzer]

So you want to find the total sales by month for each city for each month, and return the lowest/highest monthly total?

 

[Rsdg]

So you want to find the total sales by month for each city for each month, and return the lowest/highest monthly total

yes,Exactly

 

[alansidman]

Trying to understand your needs. My interpretation

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	month	city code	sale		city code	month	Total Sales		city code	Lowest Sale	Highest Sale
2	2021-01	1	15526870		1	2021-01	40443283		1	3000000	67408305
3	2021-01	1	21578633		2	2021-01	1897163		2	1897163	53394934
4	2021-01	1	3337780		3	2021-01	29591811		3	29591811	63893644
5	2021-01	2	1897163		1	2021-02	67408305
6	2021-01	3	14323674		2	2021-02	20244719
7	2021-01	3	15268137		2	2021-03	2451696
8	2021-02	1	54982451		3	2021-03	63893644
9	2021-02	1	12425854		1	2021-04	11126214
10	2021-02	2	12432354		1	2021-05	3000000
11	2021-02	2	7812365		2	2021-05	53394934
12	2021-03	2	951327		3	2021-05	38820084
13	2021-03	2	1500369
14	2021-03	3	1946358
15	2021-03	3	21499128
16	2021-03	3	4551746
17	2021-03	3	35896412
18	2021-04	1	4000365
19	2021-04	1	1500000
20	2021-04	1	2500005
21	2021-04	1	3125844
22	2021-05	1	1000000
23	2021-05	1	2000000
24	2021-05	2	5648565
25	2021-05	2	3669540
26	2021-05	2	15268700
27	2021-05	2	15264471
28	2021-05	2	13543658
29	2021-05	3	13131239
30	2021-05	3	25688845
Sheet1

This is a power query solution that requires two different queries.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sale", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"city code", "month"}, {{"Total Sales", each List.Sum([sale]), type nullable number}})
in
    #"Grouped Rows"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sale", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"city code", "month"}, {{"Total Sales", each List.Sum([sale]), type nullable number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"city code"}, {{"Lowest Sale", each List.Min([Total Sales]), type nullable number}, {"Highest Sale", each List.Max([Total Sales]), type nullable number}})
in
    #"Grouped Rows1"

 

[Rsdg]

Trying to understand your needs. My interpretation

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	month	city code	sale		city code	month	Total Sales		city code	Lowest Sale	Highest Sale
2	2021-01	1	15526870		1	2021-01	40443283		1	3000000	67408305
3	2021-01	1	21578633		2	2021-01	1897163		2	1897163	53394934
4	2021-01	1	3337780		3	2021-01	29591811		3	29591811	63893644
5	2021-01	2	1897163		1	2021-02	67408305
6	2021-01	3	14323674		2	2021-02	20244719
7	2021-01	3	15268137		2	2021-03	2451696
8	2021-02	1	54982451		3	2021-03	63893644
9	2021-02	1	12425854		1	2021-04	11126214
10	2021-02	2	12432354		1	2021-05	3000000
11	2021-02	2	7812365		2	2021-05	53394934
12	2021-03	2	951327		3	2021-05	38820084
13	2021-03	2	1500369
14	2021-03	3	1946358
15	2021-03	3	21499128
16	2021-03	3	4551746
17	2021-03	3	35896412
18	2021-04	1	4000365
19	2021-04	1	1500000
20	2021-04	1	2500005
21	2021-04	1	3125844
22	2021-05	1	1000000
23	2021-05	1	2000000
24	2021-05	2	5648565
25	2021-05	2	3669540
26	2021-05	2	15268700
27	2021-05	2	15264471
28	2021-05	2	13543658
29	2021-05	3	13131239
30	2021-05	3	25688845
Sheet1

This is a power query solution that requires two different queries.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sale", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"city code", "month"}, {{"Total Sales", each List.Sum([sale]), type nullable number}})
in
    #"Grouped Rows"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sale", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"city code", "month"}, {{"Total Sales", each List.Sum([sale]), type nullable number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"city code"}, {{"Lowest Sale", each List.Min([Total Sales]), type nullable number}, {"Highest Sale", each List.Max([Total Sales]), type nullable number}})
in
    #"Grouped Rows1"

thanks, I think this is the solution , But I am trying to understand Because I am not familiar with power Query, thank you again

 

[alansidman]

This should help to understand Power Query

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.