Fiscal Quarter Percentage difference

[Walker_Ice]

Hi Everyone,

I have a table that calculates the revenue and expenses per quarter of the selected fiscal years. For the selected fiscal year, its easy to get what the profit percentage is for the quarters. But I want to be able to calculate the profit percentage difference is for the selected year and the previous year.
For example, If select "2023" as the year. I want be able to calculate the profit percentage is for the 2023 quarter (AMJ) and what the profit percentage is for the previous year quarter ('2022 AMJ" - in this example). I want to know what the percentage difference is between these two percentages. Is the business up during the quarter or is it down and by how much?

Any help would be appreciated. Thank you in advance.

TEsting_Excel_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Income Services						Expense_Dates	Expense_TotalSpent
2	Service	Date	Payment	Tips	Total		2/4/21	$858.00
3	MDL	4-Feb-21	$900.00	$0.00	$900.00		3/13/23	$750.00
4	LDL	13-Mar-23	$300.00	$25.00	$325.00		6/17/22	$75,000.00		FISCAL YEAR QUARTERS:	2023
5	POL	17-Jun-22	$200.00	$25.00	$225.00		6/7/23	$7,000.00			Revenue	Total Cost	Profits	Current Quarter profit %	Quarter difference profit %
6	POW	7-Jun-23	$900.00	$0.00	$900.00		7/10/23	$85,000.00		JFM	$917,101.00	($126,560.00)	$790,541.00	86.2%
7	MAN	10-Jul-23	$1,900.00	$0.00	$1,900.00		3/31/24	$851,002.00		AMJ	$2,700.00	($13,844.00)	($11,144.00)	-412.7%
8	INO	31-Mar-24	$23.00	$10.00	$33.00		5/26/23	$6,520.00		JAS	$1,900.00	($85,000.00)	($83,100.00)	-4373.7%
9	YOU	26-May-23	$875.00	$10.00	$885.00		2/18/23	$1,000.00		OND	$0.00	($77.00)	($77.00)	#DIV/0!
10	TBW	18-Feb-23	$750.00	$10.00	$760.00		6/1/23	$324.00		Year Total:	$921,701.00	($225,481.00)	$696,220.00	75.5%
11	DPW	1-Jun-23	$600.00	$10.00	$610.00		2/18/24	$4,243.00
12	MDL	18-Feb-24	$600.00	$5.00	$605.00		3/18/23	$4,537.00
13	RDL	18-Mar-23	$850,999.00	$5.00	$851,004.00		5/28/22	$8,789.00
14	NAM	28-May-22	$85,000.00	$0.00	$85,000.00		2/8/23	$75.00
15	PPP	8-Feb-23	$10,000.00	$10.00	$10,010.00		3/3/23	$433.00
16	PPE	3-Mar-23	$10,090.00	$10.00	$10,100.00		1/4/21	$2,224.00
17	PPE	4-Jan-21	$100.00	$0.00	$100.00		2/14/23	$42,235.00
18	INO	14-Feb-23	$23,998.00	$0.00	$23,998.00		11/1/23	$77.00
19	LDL	1-Apr-23	$300.00	$5.00	$305.00		1/3/22	$78.00
20	HDL	3-Jan-22	$8,500.00	$0.00	$8,500.00		2/25/23	$987.00
21	AND	25-Feb-23	$20,000.00	$0.00	$20,000.00		6/26/25	$4,567.00
22	WWW	26-Jun-25	$79,000.00	$5.00	$79,005.00		3/10/23	$76,543.00
23	WDL	10-Mar-23	$899.00	$5.00	$904.00		2/2/25	$9,357.00
Sheet5

Cell Formulas
Range	Formula
K6:K9	K6	=IFERROR( SUMPRODUCT( Income_TotalPay * IF($K$4="All", 1, (YEAR(Income_Dates) = $K$4)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
L6:L9	L6	=IFERROR( -SUMPRODUCT( Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
M6:M10,E3:E23	M6	=SUM(K6:L6)
N6:N10	N6	=M6/K6
K10:L10	K10	=SUM(K6:K9)

Named Ranges
Name	Refers To	Cells
Expense_Dates	=Sheet5!$G$2:$G$22	L6:L9
Expense_TotalSpent	=Sheet5!$H$2:$H$22	L6:L9
Income_Dates	=Sheet5!$B$3:$B$23	K6:K9
Income_TotalPay	=Sheet5!$E$3:$E$23	K6:K9

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

 

[Walker_Ice]

HI,

YES that is exactly what my goal is. In the minisheet that I have coped below, you will see all the different attempts I have tried to get this to work but I have had no luck. That is why I am still reaching out in hopes of getting some help in accomplishing this complicated task.

As you can see, the table I have set up, is only to have these columns and rows. I do not have anymore space to add anymore columns, that constraint is what makes this extra challenging. Please let me know if you have any further questions. As you will see some of the formulas I couldn't get them to function properly and didn't want to lose the work so I just removed the equal sign.

Thank you in advanced for your help.

TEsting_Excel_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Income Services						Expense_Dates	Expense_TotalSpent
2	Service	Date	Payment	Tips	Total		2/4/21	$858.00
3	MDL	4-Feb-21	$900.00	$0.00	$900.00		3/13/23	$750.00
4	LDL	13-Mar-23	$300.00	$25.00	$325.00		6/17/22	$7,500.00		FISCAL YEAR QUARTERS:	2023
5	POL	17-Jun-22	$200.00	$25.00	$225.00		6/7/23	$7,000.00			Revenue	Total Cost	Profits	Current Quarter profit %	Quarter difference profit %
6	POW	7-Jun-23	$9,000.00	$0.00	$9,000.00		7/10/23	$800.00		JFM	$917,101.00	($126,560.00)	$790,541.00	86.2%	IFERROR( -( (SUMPRODUCT(Income_TotalPay * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6))) - SUMPRODUCT(Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) =$K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6)))) / SUMIFS(Expense_TotalSpent, YEAR(Expense_Dates), $K$4, ROUNDUP(MONTH(Expense_Dates)/3, 0), ROWS($J$6:$J6)) ) - ( (SUMPRODUCT(Income_TotalPay * IF($K$4="All", 1, (YEAR(Expense_Dates) = ($K$4 - 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6))) - SUMPRODUCT(Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = ($K$4 - 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6)))) / SUMIFS(Expense_TotalSpent, YEAR(Expense_Dates), $K$4-1, ROUNDUP(MONTH(Expense_Dates)/3, 0), ROWS($J$6:$J6)) ), 0)
7	MAN	10-Jul-23	$1,900.00	$0.00	$1,900.00		3/31/24	$8,500.00		AMJ	$10,800.00	($13,844.00)	($3,044.00)	-28.2%	-30133
8	INO	31-Mar-24	$23.00	$10.00	$33.00		5/26/23	$6,520.00		JAS	$1,900.00	($800.00)	$1,100.00	57.9%	IFERROR( ( (SUMPRODUCT(Income_TotalPay * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J8))) - SUMPRODUCT(Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J8)))) / SUMIFS(Expense_TotalSpent, YEAR(Expense_Dates), $K$4, ROUNDUP(MONTH(Expense_Dates)/3, 0), ROWS($J$6:$J8)) ) - ( (SUMPRODUCT(Income_TotalPay * IF($K$4="All", 1, (YEAR(Expense_Dates) = ($K$4- 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J8))) - SUMPRODUCT(Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = ($K$4 - 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J8)))) / SUMIFS(Expense_TotalSpent, YEAR(Expense_Dates), $K$4-1, ROUNDUP(MONTH(Expense_Dates)/3, 0), ROWS($J$6:$J8)) ), 0 )
9	YOU	26-May-23	$875.00	$10.00	$885.00		2/18/23	$1,000.00		OND	$0.00	($77.00)	($77.00)	#DIV/0!	n/a
10	TBW	18-Feb-23	$750.00	$10.00	$760.00		6/1/23	$324.00		Year Total:	$929,801.00	($141,281.00)	$788,520.00	84.8%	#DIV/0!
11	DPW	1-Jun-23	$600.00	$10.00	$610.00		2/18/24	$4,243.00
12	MDL	18-Feb-24	$9,000.00	$5.00	$9,005.00		3/18/23	$4,537.00
13	RDL	18-Mar-23	$850,999.00	$5.00	$851,004.00		5/28/22	$8,789.00
14	NAM	28-May-22	$85,000.00	$0.00	$85,000.00		2/8/23	$75.00
15	PPP	8-Feb-23	$10,000.00	$10.00	$10,010.00		3/3/23	$433.00
16	PPE	3-Mar-23	$10,090.00	$10.00	$10,100.00		1/4/21	$2,224.00
17	PPE	4-Jan-21	$100.00	$0.00	$100.00		2/14/23	$42,235.00
18	INO	14-Feb-23	$23,998.00	$0.00	$23,998.00		11/1/23	$77.00
19	LDL	1-Apr-23	$300.00	$5.00	$305.00		1/3/22	$78.00
20	HDL	3-Jan-22	$8,500.00	$0.00	$8,500.00		2/25/23	$987.00
21	AND	25-Feb-23	$20,000.00	$0.00	$20,000.00		6/26/25	$4,567.00
22	WWW	26-Jun-25	$79,000.00	$5.00	$79,005.00		3/10/23	$76,543.00
23	WDL	10-Mar-23	$899.00	$5.00	$904.00		2/2/25	$9,357.00
Sheet5

Cell Formulas
Range	Formula
K6:K9	K6	=IFERROR( SUMPRODUCT( Income_TotalPay * IF($K$4="All", 1, (YEAR(Income_Dates) = $K$4)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
L6:L9	L6	=IFERROR( -SUMPRODUCT( Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
E3:E23,M6:M10	M6	=SUM(K6:L6)
N6:N10	N6	=M6/K6
O7	O7	=IFERROR( -SUMPRODUCT(Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J7))) + -SUMPRODUCT(Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = ($K$4 - 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J7))), 0 )
O9	O9	=IFERROR( N9 - ( 1 - SUMPRODUCT( Expense_TotalSpent, (YEAR(Expense_Dates) = $K$4-1) * (ROUNDUP(MONTH(Expense_Dates) / 3, 0) = ROWS($J$6:$J9)) ) ) / SUMPRODUCT( Income_TotalPay, (YEAR(Income_Dates) = $K$4-1) * (ROUNDUP(MONTH(Income_Dates) / 3, 0) = ROWS($J$6:$J9)) ), "n/a" )
K10:L10	K10	=SUM(K6:K9)
O10	O10	=SUMPRODUCT(Expense_TotalSpent,(YEAR(Expense_Dates)=$K$4-1)*IF($K$4="ALL",1,ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$4))/SUMPRODUCT(Income_TotalPay,(YEAR(Income_Dates)=$K$4-1)*IF($K$4="ALL",1,ROUNDUP(MONTH(Income_Dates)/3,0)=$K$4))-SUMPRODUCT(Expense_TotalSpent,(YEAR(Expense_Dates)=$K$4)*IF($K$4="ALL",1,ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$4))/SUMPRODUCT(Income_TotalPay,(YEAR(Income_Dates)=$K$4)*IF($K$4="ALL",1,ROUNDUP(MONTH(Income_Dates)/3,0)=$K$4))

Named Ranges
Name	Refers To	Cells
Expense_Dates	=Sheet5!$G$2:$G$22	O7, L6:L9, O9:O10
Expense_TotalSpent	=Sheet5!$H$2:$H$22	O7, L6:L9, O9:O10
Income_Dates	=Sheet5!$B$3:$B$23	K6:K9, O9:O10
Income_TotalPay	=Sheet5!$E$3:$E$23	K6:K9, O9:O10

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

 

[Walker_Ice]

Hi sir,

Have you had a chance to look at the formulas that I shared on here?

 

[Walker_Ice]

Here are some formulas that I tried to use. They both do not result in the correct results.

=IFERROR( ( SUMPRODUCT( Income_TotalPay * IF($O$22="All", 1, (YEAR(Income_Dates) = $O$22)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($K$24:$K24)) ) - SUMPRODUCT( Expense_TotalSpent * IF($O$22="All", 1, (YEAR(Expense_Dates) = $O$22)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K24)) ) ) / ( SUMPRODUCT( Income_TotalPay * IF($P$22="All", 1, (YEAR(Income_Dates) = $P$22)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($K$24:$K24)) ) - SUMPRODUCT( Expense_TotalSpent * IF($P$22="All", 1, (YEAR(Expense_Dates) = $P$22)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K24)) ) ) - 1, 0)






=IFERROR( ( -SUMPRODUCT( Expense_TotalSpent * IF($O$22="All", 1, (YEAR(Expense_Dates) = $O$22)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K24)) ) / SUMPRODUCT( Income_TotalPay * IF($O$22="All", 1, (YEAR(Income_Dates) = $O$22)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($K$24:$K24)) ) ) - 1, 0)