SOLVED

Return cells that fall within a specified date range

Copper Contributor

I'm trying to extract income and expenses between two dates from a single column, then populate separate income and expense columns, removing all empty cells. I'd like to avoid using macros or pivot tables- just ordinary formulas.

Is there a formula that can identify dates within the desired range, identify rows of income (or expense), return the corresponding amount for each identified row, remove all empty cells (since not all income or expense items are adjacent to one another in the Inc/Exp column), then output those amounts under the Income (or Expense) Items columns?

 

 Given  DESIREDOUTCOME
DateAmountInc/Exp 2/1/22-5/31/22 Income Items2/1/22-5/31/22 Expense Items
1/7/22$850Income $850$85
1/7/22$85Expense $850$130
2/7/22$850Income $850$85
2/7/22$85Expense $850$85
2/18/22$85Expense  $85
3/7/22$850Income  $563
3/7/22$85Expense   
4/7/22$850Income   
4/7/22$85Expense   
5/7/22$850Income   
5/7/22$85Expense   
5/9/22$85Expense   
6/7/22$850Income   
6/7/22$85Expense   
7/7/22$850Income   
7/7/22$85Expense   
8/7/22$850Income   
8/7/22$85Expense   
9/7/22$850Income   
9/7/22$85Expense   
10/1/22$85Expense   
10/7/22$850Income   
10/7/22$85Expense   
11/7/22$850Income   
11/7/22$85Expense   
12/7/22$850Income   
12/7/22$85Expense   
5 Replies
best response confirmed by GSPmagik (Copper Contributor)
Solution

@GSPmagik 

I have no idea where the $130 and $563 come from, but take a look at the attached sample workbook. It uses the FILTER function, available in Excel in Microsoft 365, Office 2021 and Excel Online.

upload a sample file

oh I downloaded your file and thought oh he has it. I only have version 2019

 

I used =IF($B27="","",IF(C27="","",IF(Final!F25="",IF(D27="Interest",SUMIFS($V$25:$V$759,$U$25:$U$759,MONTH(C27),$S$25:$S$759,YEAR(C27)),""),Final!F25))) in one of my Amortization schedules to calculate the interest

which looks at the actual loan activity (tab final) then checks the month and year

so maybe something like

=IF(D27="Interest",SUMIFS($V$25:$V$759,$U$25:$U$759,MONTH(C27),$S$25:$S$759,YEAR(C27)),""),Final!F25))) but that would probably add it together

I had never heard of the Filter() function, thank you! It worked like a charm.

Regarding the $130 and $563- they were errors I made transcribing the data into this table. Thank you for coping with my mistakes and delivering a perfect solution!
1 best response

Accepted Solutions
best response confirmed by GSPmagik (Copper Contributor)
Solution

@GSPmagik 

I have no idea where the $130 and $563 come from, but take a look at the attached sample workbook. It uses the FILTER function, available in Excel in Microsoft 365, Office 2021 and Excel Online.

View solution in original post