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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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

SanjayGMusafir
  • Solved
Filtering Dates within the dates and adding corresponding values
Replies
4
Views
69
SanjayGMusafir
SanjayGMusafir
Cubist
  • Solved
Dynamic Formula To Label Rolling 3 Months.
Replies
1
Views
141
Eric W
Eric W
L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
139
Peter_SSs
Peter_SSs
S
  • Solved
lookup return from range of dates
Replies
4
Views
164
Peter_SSs
Peter_SSs
P
  • Locked
  • Question
Formula to build table of months
Replies
1
Views
142
Fluff
Fluff

Forum statistics

Threads
1,215,641
Messages
6,125,982
Members
449,276
Latest member
surendra75

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