Mar 15 2022 04:05 PM - edited Mar 15 2022 04:16 PM
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 |
Mar 15 2022 04:34 PM
SolutionI 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.
Mar 15 2022 04:37 PM
<grin>
Mar 15 2022 04:41 PM - edited Mar 15 2022 04:50 PM
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
Mar 15 2022 07:05 PM
Mar 15 2022 04:34 PM
SolutionI 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.