Multiple criteria sales rate

[RasGhul]

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;

Book1
	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
Pricelist

Cell Formulas
Range	Formula
C2	=EDATE(B2,1)-1

Book1
	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
Service

Cell Formulas
Range	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)

 

[excelakos]

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
Service Code	Rate Valid From (dd/mm/yy)	Rate Valid to (dd/mm/yy)
A
A
A
B
B

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Rate[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]15-03-19[/TD]
[TD="align: right"]14-04-19[/TD]
[TD="align: right"]5.25[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]15-02-19[/TD]
[TD="align: right"]14-03-19[/TD]
[TD="align: right"]4.75[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]15-01-19[/TD]
[TD="align: right"]14-02-19[/TD]
[TD="align: right"]4.5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]03-04-19[/TD]
[TD="align: right"]02-05-19[/TD]
[TD="align: right"]9.5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]12-06-18[/TD]
[TD="align: right"]11-07-18[/TD]
[TD="align: right"]8.5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Pricelist​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=EDATE(B2,1)-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C
Service Code	Date of Sale
A
A
A
B
B
B

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Cost[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12-03-19[/TD]
[TD="align: right"]4.75[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]25-01-19[/TD]
[TD="align: right"]4.5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18-03-19[/TD]
[TD="align: right"]5.25[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]02-05-19[/TD]
[TD="align: right"]9.5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]07-04-18[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]27-06-18[/TD]
[TD="align: right"]8.5[/TD]

</tbody>

Service​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUMPRODUCT((B2>=Pricelist!$B$2:$B$6)*(B2<=Pricelist!$C$2:$C$6)*(A2=Pricelist!$A$2:$A$6)*Pricelist!$D$2:$D$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much. I confirm that it works fine.
I even organized data into tables in different sheets and the sumproduct worked just fine.
Something additional is there any way to achieve same results if we have text in cell?

Imagine that instead of rates (numbers) in the price list we have something like notes or descriptions and so it is in text format.

 

[RasGhul]

Maybe this, just change =IF(C2=0,"No Valid Rate" to =IF(C2=0,"", if you prefer a blank service codes that don't have a valid date/rate.

Book1
	A	B	C	D	E
1	Service Code	Rate Valid From (dd/mm/yy)	Rate Valid to (dd/mm/yy)	Rate	Description
2	A	15-03-19	14-04-19	5.25	Text1
3	A	15-02-19	14-03-19	4.75	Text2
4	A	15-01-19	14-02-19	4.5	Text3
5	B	03-04-19	02-05-19	9.5	Text4
6	B	12-06-18	11-07-18	8.5	Text5
Pricelist

Book1
	A	B	C	D
1	Service Code	Date of Sale	Cost	Description
2	A	12-03-19	4.75	Text2
3	A	25-01-19	4.5	Text3
4	A	18-03-19	5.25	Text1
5	B	02-05-19	9.5	Text4
6	B	07-04-18	0	No Valid Rate
7	B	27-06-18	8.5	Text5
Service

Cell Formulas
Range	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)
D2	=IF(C2=0,"No Valid Rate",INDEX(Pricelist!$E$2:$E$6,SUMPRODUCT((B2>=Pricelist!$B$2:$B$6)*(B2<=Pricelist!$C$2:$C$6)*(A2=Pricelist!$A$2:$A$6)*(ROW(Pricelist!$A$2:$A$6)-ROW(Pricelist!$A$1))),))