Find the mean and median based on criteria

[Goddess]

Hi,

I have a set of data where I need to find the mean and median, based on certain criteria.

Fruit	Year	Price		Fruit	Year	Mean	Median		Overall
Apple	11/4/2022	20		Apple	2015
Pear	23/3/2015	35			2016				Fruit	Mean	Median
Grape	9/12/2019	61			2017				Apple
Apple	19/7/2021	21			2018				Grape
Apple	20/9/2021	85			2019				Pear
Pear	26/3/2018	62			2020
Apple	21/3/2016	31			2021
Pear	20/6/2016	11			2022
Grape	3/4/2017	25		Grape	2015
Grape	11/9/2017	85			2016
Grape	14/11/2016	64			2017
Pear	24/9/2018	12			2018
Pear	5/8/2019	58			2019
Pear	28/7/2022	87			2020
Apple	5/5/2020	42			2021
Pear	6/12/2021	65			2022
Grape	22/2/2022	65		Pear	2015
					2016
					2017
					2018
					2019
					2020
					2021
					2022

Basically, I need to find the mean and median price for "Apple", "Pear", and "Grape" based on the year purchased, and then the overall for all 3 fruits (cells in yellow). Can anyone help with the formula? Thanks!

 

[RobP]

Hi, would this get you where you need to be ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Fruit	Year	Price		Fruit	Year	Mean	Median		Overall
3	Apple	11/04/2022	20		Apple	2015	0	0
4	Pear	23/03/2015	35			2016	31	31		Fruit	Mean	Median
5	Grape	09/12/2019	61			2017	0	0		Apple	36.5	36.5
6	Apple	19/07/2021	21			2018	0	0		Grape	61.1111111	64
7	Apple	20/09/2021	85			2019	0	0		Pear	48.8333333	47.5
8	Pear	26/03/2018	62			2020	42	42
9	Apple	21/03/2016	31			2021	53	53
10	Pear	20/06/2016	11			2022	20	20
11	Grape	03/04/2017	25		Grape	2015	0	0
12	Grape	11/09/2017	85			2016	64	64
13	Grape	14/11/2016	64			2017	58.33333	65
14	Pear	24/09/2018	12			2018	0	0
15	Pear	05/08/2019	58			2019	61	61
16	Pear	28/07/2022	87			2020	0	0
17	Apple	05/05/2020	42			2021	0	0
18	Pear	06/12/2021	65			2022	0	0
19	Grape	22/02/2017	65		Pear	2015	35	35
20						2016	11	11
21						2017	0	0
22						2018	37	37
23						2019	58	58
24						2020	0	0
25						2021	65	65
26						2022	87	87
Sheet1

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(SUM(IF(YEAR(B$3:B$19)=F3,IF(A$3:A$19=E$3,C$3:C$19)))/COUNT(IF(YEAR(B$3:B$19)=F3,IF(A$3:A$19=E$3,C$3:C$19))),0)
H3:H10	H3	=IFERROR(MEDIAN(IF(YEAR(B$3:B$19)=F3,IF(A$3:A$19=E$3,C$3:C$19))),0)
G11:G18	G11	=IFERROR(SUM(IF(YEAR(B$3:B$19)=F11,IF(A$3:A$19=E$11,C$3:C$19)))/COUNT(IF(YEAR(B$3:B$19)=F11,IF(A$3:A$19=E$11,C$3:C$19))),0)
H11:H18	H11	=IFERROR(MEDIAN(IF(YEAR(B$3:B$19)=F11,IF(A$3:A$19=E$11,C$3:C$19))),0)
G19:G26	G19	=IFERROR(SUM(IF(YEAR(B$3:B$19)=F19,IF(A$3:A$19=E$19,C$3:C$19)))/COUNT(IF(YEAR(B$3:B$19)=F19,IF(A$3:A$19=E$19,C$3:C$19))),0)
H19:H26	H19	=IFERROR(MEDIAN(IF(YEAR(B$3:B$19)=F19,IF(A$3:A$19=E$19,C$3:C$19))),0)
K5	K5	=SUM(G3:G10)/COUNTIF(G3:G10,">0")
L5	L5	=MEDIAN(FILTER(H3:H10,H3:H10>0))
K6	K6	=SUM(G11:G18)/COUNTIF(G11:G18,">0")
L6	L6	=MEDIAN(FILTER(H11:H18,H11:H18>0))
K7	K7	=SUM(G19:G26)/COUNTIF(G19:G26,">0")
L7	L7	=MEDIAN(FILTER(H19:H26,H19:H26>0))

 

[RobP]

apologies, I saw you have Office 2019, so the FILTER formula in Overal medians won't work for you, so heres an alternative for those formulas :

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Fruit	Year	Price		Fruit	Year	Mean	Median		Overall
3	Apple	11/04/2022	20		Apple	2015	0	0
4	Pear	23/03/2015	35			2016	31	31		Fruit	Mean	Median
5	Grape	09/12/2019	61			2017	0	0		Apple	36.5	36.5
6	Apple	19/07/2021	21			2018	0	0		Grape	61.1111111	64
7	Apple	20/09/2021	85			2019	0	0		Pear	48.8333333	47.5
8	Pear	26/03/2018	62			2020	42	42
9	Apple	21/03/2016	31			2021	53	53
10	Pear	20/06/2016	11			2022	20	20
11	Grape	03/04/2017	25		Grape	2015	0	0
12	Grape	11/09/2017	85			2016	64	64
13	Grape	14/11/2016	64			2017	58.33333	65
14	Pear	24/09/2018	12			2018	0	0
15	Pear	05/08/2019	58			2019	61	61
16	Pear	28/07/2022	87			2020	0	0
17	Apple	05/05/2020	42			2021	0	0
18	Pear	06/12/2021	65			2022	0	0
19	Grape	22/02/2017	65		Pear	2015	35	35
20						2016	11	11
21						2017	0	0
22						2018	37	37
23						2019	58	58
24						2020	0	0
25						2021	65	65
26						2022	87	87
Sheet1

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(SUM(IF(YEAR(B$3:B$19)=F3,IF(A$3:A$19=E$3,C$3:C$19)))/COUNT(IF(YEAR(B$3:B$19)=F3,IF(A$3:A$19=E$3,C$3:C$19))),0)
H3:H10	H3	=IFERROR(MEDIAN(IF(YEAR(B$3:B$19)=F3,IF(A$3:A$19=E$3,C$3:C$19))),0)
G11:G18	G11	=IFERROR(SUM(IF(YEAR(B$3:B$19)=F11,IF(A$3:A$19=E$11,C$3:C$19)))/COUNT(IF(YEAR(B$3:B$19)=F11,IF(A$3:A$19=E$11,C$3:C$19))),0)
H11:H18	H11	=IFERROR(MEDIAN(IF(YEAR(B$3:B$19)=F11,IF(A$3:A$19=E$11,C$3:C$19))),0)
G19:G26	G19	=IFERROR(SUM(IF(YEAR(B$3:B$19)=F19,IF(A$3:A$19=E$19,C$3:C$19)))/COUNT(IF(YEAR(B$3:B$19)=F19,IF(A$3:A$19=E$19,C$3:C$19))),0)
H19:H26	H19	=IFERROR(MEDIAN(IF(YEAR(B$3:B$19)=F19,IF(A$3:A$19=E$19,C$3:C$19))),0)
K5	K5	=SUM(G3:G10)/COUNTIF(G3:G10,">0")
L5	L5	=MEDIAN((INDEX(IF(H3:H10<>0,H3:H10,""),0)))
K6	K6	=SUM(G11:G18)/COUNTIF(G11:G18,">0")
L6	L6	=MEDIAN((INDEX(IF(H11:H18<>0,H11:H18,""),0)))
K7	K7	=SUM(G19:G26)/COUNTIF(G19:G26,">0")
L7	L7	=MEDIAN((INDEX(IF(H19:H26<>0,H19:H26,""),0)))

 

[Goddess]

Hi Rob,

Thanks so much. For a moment it didn't work, until I realised I need to click shift+ctrl+enter for the array formula to work!

Thank you so much!

 

[RobP]

Your welcome

Glad we could help
Rob