I have the following table:
ID 268 287 396
Simulation | Leaving Date | Benefit on Leaving (if any) | Leaving Date | Benefit on Leaving (if any) | Leaving Date | Benefit on Leaving (if any)
1 2040 $50 2040 $100 2035
I want to sum up the benefits on leaving for each ID for any given year. For example, if I would like to sum up the benefits on leaving for 2035, it would be 0. However, if I were to sum up the benefits on leaving for 2040, it should give $50 $100 = $150.
How can I do this in Excel? I know how to use INDEX & MATCH to find a particular leaving date and take the benefit on leaving, which is simply the next cell. However, in some cases, there will not be just one person leaving in that given year (the INDEX & MATCH method I know of can only search and return unique values). Moreover, for certain IDs, when they leave, they do not receive any benefit, so that corresponding cell is blank, as seen for ID 396.
I would not like to "brute force" this, as what I have shown is only a short example, but in actual fact, the row for each simulation is about 100 columns long (I have about 50 IDs) and I need to do this for 1000 simulations.
In summary,
- The "Benefit on Leaving (if any)" column always follows a "Leaving Date" column.
- Each "Leaving Date" column is like my criterion - within each row, I need to find all the "leaving dates" that I am looking for and sum the corresponding benefits on leaving. If there is no benefit on leaving for that leaving date, then I take that specific value to be 0.
Any intuitive suggestions will be greatly appreciated :) and I apologise if a similar question has been asked before! I could not find any satisfactory solutions after much searching.
CodePudding user response：
Option 1: Use SUMIFS with offset ranges (though does assume that no Benefit will be equal to 2040).
=SUMIFS(B2:F2,A2:E2,2040)
Option 2: Use SUMPRODUCT with offset ranges.
=SUMPRODUCT(B2:F2*(A1:E1="Leaving Date")*(A2:E2=2040))
