Forum Discussion
Sum of Total Paid for each ID
Hello I'm trying to calculate Total paid amount 6m prior and 6m after initial based on the Paid, 6m prior to initial and 6m post initial columns.
Please see below. Please advise a formula Thank you
Expected output is for ID 1 since 6m prior to initial have 1's on all 3 cells (6m prior to Initia) total paid amount 6m prior to intial =$695
ID 2 will have total paid amount 6m post initial = $110 since the 6m post initial cells are 1's
| ID | Yearmonth | Paid | 6m prior to Initial | 6m after initial | total paid amount 6m prior to inital | total paid amount 6m post initial |
1 | 202205 | 375 | 1 | 0 | ||
| 1 | 202205 | 320 | 1 | 0 | ||
| 2 | 202101 | 50 | 0 | 1 | ||
| 2 | 202106 | 60 | 0 | 1 |
3 Replies
I would put these sums elsewhere on the spreadsheet, since you are basically (in the case of your examples at any rate), displaying two rows on the input end of things but expecting one row at the output.
This is the formula I used (which does require Excel 2021 or newer).
=SUM(FILTER(Table1[Paid],(Table1[ID]=J2)*(Table1[6m prior to Initial]=1),0))
Below is an image of the sheet, and I've attached the actual spreadsheet.
