LOGIC/SUM/FILTER Help

W

Walker_Ice

Board Regular
Joined
Oct 6, 2023
Messages
50
Office Version
  1. 2021
Platform
  1. MacOS
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
ABCDEF
1Expense_DatesExpense_TotalSpent
22/4/21$858.00FISCAL YEAR QUARTERS:2023
33/13/23$750.00Total Cost
46/17/22$75,000.00JFM$126,560.00
56/7/23$7,000.00AMJ$13,844.00
67/10/23$85,000.00JAS$85,000.00
73/31/24$851,002.00OND$77.00
85/26/23$6,520.00Year Total:$225,481.00
92/18/23$1,000.00
106/1/23$324.00
112/18/24$4,243.00
123/18/23$4,537.00
135/28/22$8,789.00
142/8/23$75.00
153/3/23$433.00
161/4/21$2,224.00
172/14/23$42,235.00
1811/1/23$77.00
191/3/22$78.00
202/25/23$987.00
216/26/25$4,567.00
223/10/23$76,543.00
Sheet3
Cell Formulas
RangeFormula
F4F4=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) )
F5F5=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) )
F6F6=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) )
F7F7=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) )
F8F8=SUM(F4:F7)
Named Ranges
NameRefers ToCells
Expense_Dates=Sheet3!$A$2:$A$25F4:F7
Expense_TotalSpent=Sheet3!$B$2:$B$26F4:F7
Cells with Data Validation
CellAllowCriteria
F2ListAll,2022,2023,2024
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Would something like this work for you ?
Copy into F4 then copy down to F5:F7

Excel Formula: 
=LET(yr,$F$2,
          yrChk,IF(yr="All",1,YEAR($A$2:$A$22)=yr),
          qtr,ROWS($E$4:$E4),
          qtrChk,ROUNDUP(MONTH($A$2:$A$22)/3,0)=qtr,
          SUM(FILTER($B$2:$B$22,yrChk*qtrChk,0)))
 
Upvote 0
Hi Alex,
Thank you your response. Below is the breakdown, maybe you can help me, get it to function properly.

When I incorporate it into my spreadsheet it not functioning. Please take a look and let me know if I am doing something wrong. THANK YOU for your help in advance.

Expense_Dates = The column of all the date entries.
Expense_TotalSpent = The column all the payments made.



=LET(yr,$N$33, yrChk,IF(yr="All",1,YEAR(Expense_Dates)=yr), qtr,ROWS($K$35:$K35), qtrChk,ROUNDUP(MONTH(Expense_Dates)/3,0)=qtr, SUM(FILTER(Expense_TotalSpent,yrChk*qtrChk,0)))
 
Upvote 0
Below is the formula that I have working for "All" but it's not functioning when I select a specific year.


=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)
 
Upvote 0
See if you can copy the formula from here:

20231113 SumIf FIlter Year is All Walker_Ice.xlsx
KLMN
33FISCAL YEAR QUARTERS:2023
34Total Cost
35JFM126560
36AMJ13844
37JAS85000
38OND77
39Year Total:225481
Sheet1
Cell Formulas
RangeFormula
L35:L38L35=LET(yr,$N$33, yrChk,IF(yr="All",1,YEAR(Expense_Dates)=yr), qtr,ROWS($K$35:$K35), qtrChk,ROUNDUP(MONTH(Expense_Dates)/3,0)=qtr, SUM(FILTER(Expense_TotalSpent,yrChk*qtrChk,0)))
L39L39=SUM(L35:L38)
Named Ranges
NameRefers ToCells
Expense_Dates=Sheet1!$A$2:$A$22L35:L38
Expense_TotalSpent=Sheet1!$B$2:$B$22L35:L38
 
Upvote 0
How are you populating the year field ?
Does this make a difference ?

Rich (BB code): 
=LET(yr,$N$33,
          yrChk,IF(yr="All",1,YEAR(Expense_Dates)=VALUE(yr)),
          qtr,ROWS($K$35:$K35),
          qtrChk,ROUNDUP(MONTH(Expense_Dates)/3,0)=qtr,
          SUM(FILTER(Expense_TotalSpent,yrChk*qtrChk,0)))
 
Upvote 0
Alex Blakenburg said:
See if you can copy the formula from here:

20231113 SumIf FIlter Year is All Walker_Ice.xlsx
KLMN
33FISCAL YEAR QUARTERS:2023
34Total Cost
35JFM126560
36AMJ13844
37JAS85000
38OND77
39Year Total:225481
Sheet1
Cell Formulas
RangeFormula
L35:L38L35=LET(yr,$N$33, yrChk,IF(yr="All",1,YEAR(Expense_Dates)=yr), qtr,ROWS($K$35:$K35), qtrChk,ROUNDUP(MONTH(Expense_Dates)/3,0)=qtr, SUM(FILTER(Expense_TotalSpent,yrChk*qtrChk,0)))
L39L39=SUM(L35:L38)
Named Ranges
NameRefers ToCells
Expense_Dates=Sheet1!$A$2:$A$22L35:L38
Expense_TotalSpent=Sheet1!$B$2:$B$22L35:L38
Click to expand...
This formula does work. Not sure, why it wasn't working before.

Do you know why the other formula I had worked for "All" but not for specific year? Can you help me debug that formula, it bothering me because it does work unless I choose a specific year.

=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)
 
Upvote 0
Sumif / Countif etc will only work with Ranges in the Range position.
eg Expense_Dates is a range
but YEAR(ExpenseDates) is an Array

=sumifs(sum_range, criteria_range, criteria,...)
The formulas that say almost anything other than "range" seem to handle arrays fine.
 
Upvote 0
Gotcha. Is there a way to make this formula work, for a specific year and the "All" selection?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Breaking down some dates using a formula
2
Replies
13
Views
553
zelarra
Z
S
  • Question Question
Excluding dates in summing up a range
Replies
3
Views
246
Fluff
Fluff
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
387
Asbestos_Jen
Asbestos_Jen
F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
202
Peter_SSs
Peter_SSs
R
  • Solved
Large function
Replies
7
Views
324
Eric W
Eric W

Forum statistics

Threads
1,224,591
Messages
6,179,766
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top