Multiple Criteria, Including Date Range

[Savene]

Hello MrExcel,

I'm stuck on an issue. I have a log of quoted parts and a log of sales on separate tabs. The quote log has a date range for each quote, and the sales log has a date of sale. Both have part numbers for each.

I'm trying to have my quote log match criteria and return whether or not the quoted job was "Won". I'd like to do a search of the part number, then verify if the sales date was within the quote date range, if so, place "Won" in the cell.

SHEET1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part123[/TD]
[TD]3/1/19[/TD]
[TD]3/5/19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Part456[/TD]
[TD]3/2/19[/TD]
[TD]3/7/19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Part123[/TD]
[TD]3/6/19[/TD]
[TD]3/11/19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Part789[/TD]
[TD]3/4/19[/TD]
[TD]3/9/19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Part456[/TD]
[TD]3/8/19[/TD]
[TD]3/13/19[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part123[/TD]
[TD]3/10/19[/TD]
[TD]WON[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Part333[/TD]
[TD]3/1/19[/TD]
[TD]WON[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Part456[/TD]
[TD]3/9/19[/TD]
[TD]WON[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Part789[/TD]
[TD]3/8/19[/TD]
[TD]WON[/TD]
[/TR]
</tbody>[/TABLE]


This is the formula that I'm using to fill in the "Won" in SHEET1 in cells D:D

{=INDEX(Sheet2!$C$1:$C$4,MATCH(1,((Sheet2!$A$1:$A$4=G1004)*(Sales!$B$1:$B$4>=B1)*(Sheet2!$B$1:$B$4<=C1)),0))}

I've tried this a few different ways, but I'm still getting #N/A even though I've verified that several should say "Won".

Any insight or direction would be much appreciated.

Thank you!

 

[RasGhul]

Hi Savene,

Maybe this;

Book1
	A	B	C	D
1	Test	WON
2
3	Part123	01-03-19	05-03-19
4	Part456	02-03-19	07-03-19
5	Part123	06-03-19	11-03-19	WON
6	Part789	04-03-19	09-03-19	WON
7	Part456	08-03-19	13-03-19	WON
Sheet1

Cell Formulas
Range	Formula
D3	=IF(COUNTIFS(Sheet2!$B$1:$B$4,">="&Sheet1!B3,Sheet2!$B$1:$B$4,"<="&Sheet1!C3,Sheet2!$A$1:$A$4,Sheet1!A3,Sheet2!$C$1:$C$4,Sheet1!$B$1)>0,$B$1,"")

Book1
	A	B	C
1	Part123	10-03-19	WON
2	Part333	01-03-19	WON
3	Part456	09-03-19	WON
4	Part789	08-03-19	WON
Sheet2

 

[Savene]

This is a great work around! Thank you!

 

[excelakos]

Hi there
I think this thread is the closest to what i look for but still i am not sure.

I need to apply the correct service rate based on sale date ranges.

For the same service i have multiple rates based on dates and i have multiple services

So below table represents the Price Catalogue which can be updated at any time and as many times as neccessary.
The rates are valid on the date and onwards.

The oldest Date entry equals first time service was introduced

So in words Service code A costs 4.5 from 15/01/2019 till 14/02/2019.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Service Code[/TD]
[TD]Rate Valid From (dd/mm/yy)[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]15/03/2019[/TD]
[TD]5.25[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]15/02/2019[/TD]
[TD]4.75[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]15/01/2019[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]03/04/2019[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]12/06/2018[/TD]
[TD]8.5[/TD]
[/TR]
</tbody>[/TABLE]


So i want to set up a 2nd table keeping history of sales and applying the correct rate based on sale date through the price catalogue.
The table should be like this: On column Cost i need to get the price from pricelist

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Service Code[/TD]
[TD]Date of Sale[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12/03/2019[/TD]
[TD]4.75[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]25/01/2019[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]15/02/2019[/TD]
[TD]8.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]19/05/2019[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]07/04/2018[/TD]
[TD]"error"[/TD]
[/TR]
</tbody>[/TABLE]


Is this possible???

 

[RasGhul]

See if this works for you;

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
8	Service Code	Date of Sale	Cost
9	A	12-03-19	4.75
10	A	25-01-19	4.5
11	A	18-03-19	5.25
12	B	02-05-19	9.5
13	B	07-04-18	0
14	B	27-06-18	8.5
Sheet1

Cell Formulas
Range	Formula
C2	=EDATE(B2,1)-1
C9	=SUMPRODUCT((B9>=$B$2:$B$6)*(B9<=$C$2:$C$6)*(A9=$A$2:$A$6)*$D$2:$D$6)

 

[excelakos]

See if this works for you;

	A	B	C	D
Service Code	Rate Valid From (dd/mm/yy)	Rate Valid to (dd/mm/yy)
A
A
A
B
B
Service Code	Date of Sale
A
A
A
B
B
B

<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]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="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]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=SUMPRODUCT((B9>=$B$2:$B$6)*(B9<=$C$2:$C$6)*(A9=$A$2:$A$6)*$D$2:$D$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your answer

Shouldn't this function work even if sales logs belong to different sheet than pricelist?

 

[RasGhul]

Yes it will, we just need to add sheet references into the formula for your sheet names.

 

[DanteAmor]

Hi Savene:

Another option:

=IF(AND(B1<=VLOOKUP(A1,Sheet2!$A$1:$B$4,2,0),C1>=VLOOKUP(A1,Sheet2!$A$1:$B$4,2,0)),"Won","")

 

[excelakos]

Yes it will, we just need to add sheet references into the formula for your sheet names.

I dont know why i cant get it to work.
I copied as you offer it but still i get error
(I have copied the EDATE to all "C" column cells)

 

[RasGhul]

excelakos

Bump to this new thread to your solution

https://www.mrexcel.com/forum/excel-questions/1096158-multiple-criteria-sales-rate.html#post5269802