In a table, I have to filter & sum the rows. But there is a date in the range & due to this, the result is not correct. Can the experts advise how to overcome this problem?
Sample table is shown below. In the base data, there is date field between amount 1 & amount 2. In the summary table, I´m using filter to sum up the amount based on year. But due to the date field in between, the results are not correct. If I remove the date field, I´m getting the correct result from filter function. But, I can´t change the table layout.
Base data -
Summary -
Sample table is shown below. In the base data, there is date field between amount 1 & amount 2. In the summary table, I´m using filter to sum up the amount based on year. But due to the date field in between, the results are not correct. If I remove the date field, I´m getting the correct result from filter function. But, I can´t change the table layout.
Base data -
| excel problems.xlsx | ||||||
|---|---|---|---|---|---|---|
| C | D | E | F | |||
| 3 | month | amount 1 | date | amount 2 | ||
| 4 | ene-22 | 10 | 02-ene-22 | |||
| 5 | feb-23 | 20 | 13-feb-23 | 4 | ||
| 6 | mar-23 | 30 | 30-mar-23 | 6 | ||
| 7 | abr-24 | 40 | 26-abr-24 | 8 | ||
| 8 | may-24 | 50 | 17-may-24 | 3 | ||
| 9 | jun-24 | 60 | 20-jun-24 | 6 | ||
Sheet4 | ||||||
Summary -
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J4:J6 | J4 | =SUM(FILTER($D$4:$F$9,($C$4:$C$9>=DATE(I4,1,1))*($C$4:$C$9<=DATE(I4,12,31)))) |
