Hi Excelakos,
I've started a new thread so we don't continue to hijack the previous thread with a different problem.
Here is the same solution across sheets, just change the names & ranges to what you need and test if it suits;
| A | B | C | D |
---|
1 | Service Code | Rate Valid From (dd/mm/yy) | Rate Valid to (dd/mm/yy) | Rate |
2 | A | 15-03-19 | 14-04-19 | 5.25 |
3 | A | 15-02-19 | 14-03-19 | 4.75 |
4 | A | 15-01-19 | 14-02-19 | 4.5 |
5 | B | 03-04-19 | 02-05-19 | 9.5 |
6 | B | 12-06-18 | 11-07-18 | 8.5 |
7 | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Pricelist
Worksheet Formulas
Cell | Formula |
---|
C2 | =EDATE(B2,1)-1 |
---|
<thead>
</thead><tbody>
</tbody> |
<tbody>
</tbody>
| A | B | C |
---|
1 | Service Code | Date of Sale | Cost |
2 | A | 12-03-19 | 4.75 |
3 | A | 25-01-19 | 4.5 |
4 | A | 18-03-19 | 5.25 |
5 | B | 02-05-19 | 9.5 |
6 | B | 07-04-18 | 0 |
7 | B | 27-06-18 | 8.5 |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Service
Worksheet Formulas
Cell | Formula |
---|
C2 | =SUMPRODUCT((B2>=Pricelist!$B$2:$B$6)*(B2<=Pricelist!$C$2:$C$6)*(A2=Pricelist!$A$2:$A$6)*Pricelist!$D$2:$D$6) |
---|
<thead>
</thead><tbody>
</tbody> |
<tbody>
</tbody>