Forum Discussion
Return cells that fall within a specified date range
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 | DESIRED | OUTCOME | |||
| Date | Amount | Inc/Exp | 2/1/22-5/31/22 Income Items | 2/1/22-5/31/22 Expense Items | |
| 1/7/22 | $850 | Income | $850 | $85 | |
| 1/7/22 | $85 | Expense | $850 | $130 | |
| 2/7/22 | $850 | Income | $850 | $85 | |
| 2/7/22 | $85 | Expense | $850 | $85 | |
| 2/18/22 | $85 | Expense | $85 | ||
| 3/7/22 | $850 | Income | $563 | ||
| 3/7/22 | $85 | Expense | |||
| 4/7/22 | $850 | Income | |||
| 4/7/22 | $85 | Expense | |||
| 5/7/22 | $850 | Income | |||
| 5/7/22 | $85 | Expense | |||
| 5/9/22 | $85 | Expense | |||
| 6/7/22 | $850 | Income | |||
| 6/7/22 | $85 | Expense | |||
| 7/7/22 | $850 | Income | |||
| 7/7/22 | $85 | Expense | |||
| 8/7/22 | $850 | Income | |||
| 8/7/22 | $85 | Expense | |||
| 9/7/22 | $850 | Income | |||
| 9/7/22 | $85 | Expense | |||
| 10/1/22 | $85 | Expense | |||
| 10/7/22 | $850 | Income | |||
| 10/7/22 | $85 | Expense | |||
| 11/7/22 | $850 | Income | |||
| 11/7/22 | $85 | Expense | |||
| 12/7/22 | $850 | Income | |||
| 12/7/22 | $85 | Expense |
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.
5 Replies
- upload a sample file
<grin>
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 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.
- 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!
