I have a range of date cells. I have to sum up the return from these cells for each year. Sample table is given below.
Table 1
I have to use SUMIF function & NOT a pivot table for various reasons. I'm using additional column in table 1 to find the year & then using it in table 2 to find the total for each year. Table below has the details.
I would like to eliminate the additional step to calculate the year in table 1. I'm sure there must be a better way to do this by deriving the year directly in SUMIF function in table 2. Can the experts in this forum throw some light on this?
Table 2
Table 1
| Book1 | ||||||
|---|---|---|---|---|---|---|
| B | C | D | E | |||
| 2 | Sl.# | Month | Return | Year | ||
| 3 | 1 | Nov-22 | 100 | 2022 | ||
| 4 | 2 | Dec-22 | 101 | 2022 | ||
| 5 | 3 | Jan-23 | 298 | 2023 | ||
| 6 | 4 | Feb-23 | 357 | 2023 | ||
| 7 | 5 | Mar-23 | 467 | 2023 | ||
| 8 | 6 | Apr-23 | 509 | 2023 | ||
| 9 | 7 | May-23 | 943 | 2023 | ||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:B9 | B4 | =B3+1 |
| C4:C9 | C4 | =EDATE(C3,1) |
| E3:E9 | E3 | =YEAR(C3) |
I have to use SUMIF function & NOT a pivot table for various reasons. I'm using additional column in table 1 to find the year & then using it in table 2 to find the total for each year. Table below has the details.
I would like to eliminate the additional step to calculate the year in table 1. I'm sure there must be a better way to do this by deriving the year directly in SUMIF function in table 2. Can the experts in this forum throw some light on this?
Table 2
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3:C4 | C3 | =SUMIF(Sheet1!$E$3:$E$9,B3,Sheet1!$D$3:$D$9) |
