Median/Average based on a range within a filtered list

[coloray13]

I am having an issue finding the right formula to calculate the median/average correctly. I have a number of sales that I filter and need to calculate the median and average for each month, which also changes. As of now, I have tried numerous nested formulas and the Median IF Array formula is the closest one that works with the blank cells, the #NA cells, and/or the #VALUE cells in the filtered list, but it still is not correct.

Here is what I am trying to accomplish:
1. count the number of sales in month 0 (in this case, there is only 1, not 3 - see attached photo)
2. count the number of sales in months 1 thru 24 (aka months back)
3. count the rest of the sales in months 25+
4. get the median and averages for those sales (i.e. if 5 sales sold last month, what is the median of those 5 sales)

The green column in the photo is known to be correct, which is how I know my computations are not.

I have tried AGGREGATE, MEDIANIF, etc.

Any help is greatly appreciated!

 

[JamesCanale]

I made up what I guess your input data looks like. And instead of going to 25, I only went to 9 or more months back.

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G
1	Date of Sale	Amount		Months back	count	median	average
2	6/1/2020	$ 3,717		0	5	1343	1657
3	6/6/2020	$ 4,591		1	7	2513	2585.429
4	6/8/2020	$ 4,341		2	8	3184	2974.125
5	6/15/2020	$ 2,943		3	7	1924	2603
6	6/22/2020	$ 4,427		4	9	2387	2751.667
7	6/29/2020	$ 2,008		5	8	2401.5	2743
8	7/1/2020	$ 1,829		6	10	3666.5	3390.3
9	7/7/2020	$ 1,141		7	7	3710	3676.571
10	7/10/2020	$ 3,044		8	8	3286	3133.25
11	7/15/2020	$ 1,593		9 or more	12	2993.5	2944.5
12	7/21/2020	$ 2,118
13	7/25/2020	$ 3,582
14	8/1/2020	$ 1,426
15	8/4/2020	$ 3,576
16	8/9/2020	$ 2,363
17	8/9/2020	$ 2,848
18	8/11/2020	$ 3,200
19	8/18/2020	$ 3,372
20	8/24/2020	$ 4,270
21	8/29/2020	$ 4,011
22	9/4/2020	$ 4,809
23	9/10/2020	$ 2,416
24	9/12/2020	$ 3,710
25	9/15/2020	$ 3,570
26	9/22/2020	$ 3,798
27	9/27/2020	$ 4,130
28	9/28/2020	$ 3,303
29	10/2/2020	$ 2,543
30	10/3/2020	$ 4,340
31	10/7/2020	$ 4,855
32	10/11/2020	$ 2,119
33	10/16/2020	$ 2,605
34	10/22/2020	$ 3,511
35	10/22/2020	$ 1,149
36	10/22/2020	$ 4,698
37	10/28/2020	$ 3,822
38	10/29/2020	$ 4,261
39	11/1/2020	$ 2,272
40	11/3/2020	$ 4,538
41	11/10/2020	$ 2,035
42	11/14/2020	$ 2,916
43	11/17/2020	$ 2,531
44	11/21/2020	$ 3,563
45	11/27/2020	$ 1,908
46	11/28/2020	$ 2,181
47	12/2/2020	$ 4,809
48	12/2/2020	$ 1,001
49	12/3/2020	$ 1,893
50	12/9/2020	$ 3,601
51	12/15/2020	$ 1,138
52	12/15/2020	$ 3,310
53	12/21/2020	$ 4,431
54	12/25/2020	$ 2,387
55	12/29/2020	$ 2,195
56	1/1/2021	$ 1,376
57	1/5/2021	$ 1,260
58	1/7/2021	$ 3,856
59	1/14/2021	$ 1,924
60	1/21/2021	$ 3,545
61	1/26/2021	$ 1,460
62	1/26/2021	$ 4,800
63	2/1/2021	$ 3,663
64	2/8/2021	$ 1,604
65	2/9/2021	$ 3,302
66	2/12/2021	$ 4,313
67	2/15/2021	$ 4,150
68	2/18/2021	$ 1,253
69	2/22/2021	$ 2,442
70	2/28/2021	$ 3,066
71	3/6/2021	$ 3,291
72	3/7/2021	$ 2,513
73	3/13/2021	$ 2,365
74	3/16/2021	$ 3,497
75	3/21/2021	$ 2,292
76	3/24/2021	$ 2,976
77	3/29/2021	$ 1,164
78	4/3/2021	$ 1,159
79	4/4/2021	$ 1,479
80	4/9/2021	$ 1,342
81	4/10/2021	$ 2,962
82	4/17/2021	$ 1,343
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=SUMPRODUCT(--(YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))=D2))
F2:F10	F2	=MEDIAN(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))=D2),$B$2:$B$82,""))
G2:G10	G2	=AVERAGE(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))=D2),$B$2:$B$82,""))
E11	E11	=SUMPRODUCT(--(YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))>8))
F11	F11	=MEDIAN(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))>8),$B$2:$B$82,""))
G11	G11	=AVERAGE(IF((YEAR(TODAY())*12+MONTH(TODAY())-(YEAR($A$2:$A$82)*12+MONTH($A$2:$A$82))>8),$B$2:$B$82,""))