Filter/Matching HELP

[Walker_Ice]

Hi Everyone,

I recently created a table that counts based on the raw data available. Unfortunately, when I created the formula to find the average, I based it off of a table which already summed up the raw data based on month and year.
I would like to modify the formula to find the average based on the raw references that way its future proof and not relying on the table which will only be able to hold 4 years at a time.

Can someone help me modify the "Avg price per Appt" formula so that it references the raw data? References would be "Income_TotalPay & Income_Dates.

Any help would would be appreciated.

TEsting_Excel_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Income_Pay	Payment	Tips	Income_TotalPay				Appointment Analysis
2	February 4, 2023	$858.00	$0.00	$85.00				Select Year:	All
3	March 13, 2024	$750.00	$250.00	$100.00				Month:	# of Appts	Avg price per Appt
4	June 17, 2023	$75,000.00	$25.00	$100.00				JAN	0	$0.00
5	April 25, 2022	$7,000.00	$0.00	$70.00				FEB	2	$1,945.00
6	May 17, 2023	$85,000.00	$5.00	$90.00				MAR	1	$11,355.00
7	September 6, 2023	$851,002.00	$5.00	$90.00				APR	1	$74,876.00
8	February 20, 2025	$6,520.00	$5.00	$70.00				MAY	1	$105,476.00
9								JUN	1	$1,385.00
10								JUL	0	$0.00
11								AUG	0	$0.00
12								SEP	1	$55,999.00
13								OCT	0	$0.00
14								NOV	0	$0.00
15								DEC	0	$0.00
16
17
18
19
20
21
22		Business Analysis
23		Monthly Income Report		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
24		2022		$0.00	$0.00	$0.00	$4,300.00	$88,888.00	$0.00	$900.00	$750.00	$999.00	$0.00	$0.00	$0.00
25		2023		$1,400.00	$3,890.00	$4,555.00	$70,576.00	$16,588.00	$1,385.00	$4,700.00	$1,406.00	$15,000.00	$30,090.00	$50,000.00	$20,000.00
26		2024		$0.00	$0.00	$6,800.00	$0.00	$0.00	$0.00	$0.00	$0.00	$40,000.00	$0.00	$0.00	$0.00
27		2025		$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Sheet2

Cell Formulas
Range	Formula
I4:I15	I4	=IF( $I$2="All", COUNT(FILTER(Income_Dates, MONTH(Income_Dates) = MONTH(DATEVALUE("1-"&TEXT($H4, "mmm")&"-2000")))), COUNT( FILTER( Income_Dates, (MONTH(Income_Dates) = MONTH(DATEVALUE("1-"&TEXT($H4, "mmm")&"-2000"))) * (YEAR(Income_Dates) = $I$2) ) ) )
J4:J15	J4	=IFERROR( IF( $I$2="All", SUM(INDEX($D$24:$O$27, , MATCH(H4, $D$23:$O$23, 0)))/I4, INDEX($D$24:$O$27, MATCH($I$2, $B$24:$B$27, 0), MATCH(H4, $D$23:$O$23, 0))/I4 ), 0 )

Named Ranges
Name	Refers To	Cells
Income_Dates	=Sheet2!$A$2:$A$8	I4:I15

Cells with Data Validation
Cell	Allow	Criteria
I2	List	All, 2022, 2023, 2024, 2025

 

[Alex Blakenburg]

See if this does what you are after:
(Enter into J4)
If you are going to use a range name for the date column, you might want to use one for the Amount column as well, or convert the data to a table.

=IFERROR(
 IF(
    $I$2="All",
    SUM(FILTER($B$2:$B$8, MONTH(Income_Dates) = MONTH(DATEVALUE("1-"&TEXT($H4, "mmm")&"-2000"))))/I4,
    SUM(
        FILTER(
            $B$2:$B$8,
            (MONTH(Income_Dates) = MONTH(DATEVALUE("1-"&TEXT($H4, "mmm")&"-2000"))) * (YEAR(Income_Dates) = $I$2)
        )
    )
),0)

 

[Walker_Ice]

Hi Alex, Thank you for your response. I think you are on the right track but there might some confusion. Let try and clarify as best as possible.

Income_Dates = A2:A8.
Income_TotalPay = D2:D8.

I need the "Avg Price per Appt" (Cell=J4), to search through Income_Dates for the Month in cell=H4 and the Year in cell=I2. Then Divide by the number of appts in cell=I4.
If the user selects "All" in cell=I2, then all sum all the payments for that month and all the years available.

I hope this helps. Below is how I modified the code you provided but it still did not work in my worksheet.

 

[Alex Blakenburg]

Give this a try:

=LET(colPay,$D$2:$D$8,
    IFERROR(
        IF(
          $I$2="All",
          SUM(FILTER(colPay,MONTH(Income_Dates)=MONTH(DATEVALUE("1-"&TEXT($H4,"mmm")&"-2000"))))/I4,
          SUM(FILTER(colPay,(MONTH(Income_Dates)=MONTH(DATEVALUE("1-"&TEXT($H4,"mmm")&"-2000")))*(YEAR(Income_Dates)=$I$2)))/I4
),0)
)

 

[Walker_Ice]

Thank you!! That works Perfectly!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.