LOGIC/SUM/FILTER Help

[Walker_Ice]

Hi Everyone,

I have a table that is looking to sum up all of the expenses for the fiscal years of a specific year, except now I would like to be able to select "All" and sum up all of the data available for those 3 months specifically in that fiscal quarter.

Below is the code I have which works for the specific year. Can someone help with modifying it to be able to select "All" from the dropdown list.

TEsting_Excel_File.xlsx
	A	B	C	D	E	F
1	Expense_Dates	Expense_TotalSpent
2	2/4/21	$858.00			FISCAL YEAR QUARTERS:	2023
3	3/13/23	$750.00				Total Cost
4	6/17/22	$75,000.00			JFM	$126,560.00
5	6/7/23	$7,000.00			AMJ	$13,844.00
6	7/10/23	$85,000.00			JAS	$85,000.00
7	3/31/24	$851,002.00			OND	$77.00
8	5/26/23	$6,520.00			Year Total:	$225,481.00
9	2/18/23	$1,000.00
10	6/1/23	$324.00
11	2/18/24	$4,243.00
12	3/18/23	$4,537.00
13	5/28/22	$8,789.00
14	2/8/23	$75.00
15	3/3/23	$433.00
16	1/4/21	$2,224.00
17	2/14/23	$42,235.00
18	11/1/23	$77.00
19	1/3/22	$78.00
20	2/25/23	$987.00
21	6/26/25	$4,567.00
22	3/10/23	$76,543.00
Sheet3

Cell Formulas
Range	Formula
F4	F4	=SUMIFS( $B$2:$B$22, $A$2:$A$22, ">=" & DATE($F$2, 1, 1), $A$2:$A$22, "<=" & EOMONTH(DATE($F$2, 3, 1), 0) )
F5	F5	=SUMIFS( $B$2:$B$22, $A$2:$A$22, ">=" & DATE($F$2, 4, 1), $A$2:$A$22, "<=" & EOMONTH(DATE($F$2, 6, 1), 0) )
F6	F6	=SUMIFS( $B$2:$B$22, $A$2:$A$22, ">=" & DATE($F$2, 7, 1), $A$2:$A$22, "<=" & EOMONTH(DATE($F$2, 9, 1), 0) )
F7	F7	=SUMIFS( $B$2:$B$22, $A$2:$A$22, ">=" & DATE($F$2, 10, 1), $A$2:$A$22, "<=" & EOMONTH(DATE($F$2, 12, 1), 0) )
F8	F8	=SUM(F4:F7)

Named Ranges
Name	Refers To	Cells
Expense_Dates	=Sheet3!$A$2:$A$25	F4:F7
Expense_TotalSpent	=Sheet3!$B$2:$B$26	F4:F7

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

 

[Walker_Ice]

But when I tried modifying it for "All" i never got it to work.

Then I ended up with the formula below, and I got it to work for "All" but it doesn't work for any specific year. TRAPPED IN LOOP lol.

=IFERROR( IF( $N$33="All", SUM(FILTER(Expense_TotalSpent, ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$35:$K35))), SUMIFS(Expense_TotalSpent, YEAR(Expense_Dates), $N$33, ROUNDUP(MONTH(Expense_Dates)/3, 0), ROWS($K$35:$K35)) ), 0)

Can you help me find out what I am getting wrong? I would truly appreciate it.

 

[Alex Blakenburg]

If you really want 2 formulas based on using an if statement the below will work. It essentially uses your original formula if <> "All". The only difference being that you can copy it down to all quarters.

=IFERROR( IF(
$N$33="All",
SUM(FILTER(Expense_TotalSpent, ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$35:$K35))),
SUMIFS(Expense_TotalSpent,
                  Expense_Dates,">=" & DATE($N$33, ROWS($K$35:K35)*3-2, 1),
                  Expense_Dates,"<=" & EOMONTH(DATE($N$33, ROWS($K$35:K35)*3, 1),0))),
0)

 

[Alex Blakenburg]

Here is another single formula version:
(If you are using an older version of Excel you would need to change the SUM to SUMPRODUCT)

=SUM(
Expense_TotalSpent
*IF($N$33="All",1,(YEAR(Expense_Dates)=$N$33))
*(ROUNDUP(MONTH(Expense_Dates)/3,0)=ROWS($K$35:$K35)))

 

[Walker_Ice]

They both work beautifully!! Thank you so much for your support! I'm trying to get better at using Excel and you have been a tremendous help in this recent excel journey of mine. I appreciate your time and effort.

 

[Alex Blakenburg]

No problem. Happy to help.