How To Find data within Date Range and Multiple Parameters

[Antonius]

Hi All,

I'm Trying to Fill "Price" Column in Sheet1, by comparing Date, Product, Origin, Destination and Truck Code in Sheet2.
for example in first Row the price Should be 1.952,16, because the Date in Sheet1 is in Range of Start date and End Date in sheet2, and the other parameter (Product : Aqua 600 ML, Origin : "KLATEN", Destination : "GRESIK", Truck Code : "G-NDK" is also right.
Or in Row 6, the Price Should be 3.246,39

i've been working on these for almost 2 weeks with no luck, almost give up. please be kind and help me solve this.

Thank You

Sample data :
Sheet1 (Operational Data)
No. Date Product Origin Destination Truck Code Price
1 30/09/2014 Aqua 600 ML KLATEN GRESIK G-NDK 1.952,16
2 03/10/2014 Aqua 1500 ML KLATEN PASURUAN G-NDK
3 03/10/2014 Aqua 1500 ML KLATEN SURABAYA G-NDK
4 07/10/2014 Aqua 600 ML KLATEN GRESIK G-NDK
5 08/10/2014 Aqua 1500 ML KLATEN GRESIK G-NDK
6 20/11/2014 Aqua 1500 ML KLATEN PROBOLINGGO G-NDK 3.246,39
7 20/11/2014 Aqua 1500 ML KLATEN SITUBONDO G-NDK
8 21/11/2014 Aqua 1500 ML KLATEN PURWOSARI G-NDK
9 13/12/2014 Aqua 1500 ML KLATEN SURABAYA G-NDK
10 16/12/2014 Aqua 1500 ML KLATEN PASURUAN G-NDK

Sheet2 (Master Data)

Truck Code Start Date End Date Origin Destination Product Price
G-NDK 01/09/2014 17/11/2014 KLATEN GRESIK Aqua 1500 ML 2.401,28
G-NDK 01/09/2014 17/11/2014 KLATEN SURABAYA Aqua 1500 ML 2.401,28
G-NDK 01/09/2014 17/11/2014 KLATEN PASURUAN Aqua 1500 ML 2.720,48
G-NDK 01/09/2014 17/11/2014 KLATEN PROBOLINGGO Aqua 1500 ML 2.898,56
G-NDK 01/09/2014 17/11/2014 KLATEN TANGGUL Aqua 1500 ML 3.548,16
G-NDK 01/09/2014 17/11/2014 KLATEN SITUBONDO Aqua 1500 ML 3.311,84
G-NDK 01/09/2014 17/11/2014 KLATEN PURWOSARI Aqua 1500 ML 2.720,48
G-NDK 01/09/2014 17/11/2014 KLATEN SURABAYA Aqua 600 ML 1.952,16
G-NDK 01/09/2014 17/11/2014 KLATEN GRESIK Aqua 600 ML 1.952,16
G-NDK 01/09/2014 17/11/2014 KLATEN PASURUAN Aqua 600 ML 2.224,32
G-NDK 01/09/2014 17/11/2014 KLATEN TANGGUL Aqua 600 ML 2.816,44
G-NDK 01/09/2014 17/11/2014 KLATEN TANGGUL Aqua 600 ML 2.816,44
G-NDK 01/09/2014 17/11/2014 KLATEN PURWOSARI Aqua 600 ML 2.224,32
G-NDK 01/09/2014 17/11/2014 KLATEN PROBOLINGGO Aqua 600 ML 2.324,00
G-NDK 18/11/2014 18/01/2015 KLATEN GRESIK Aqua 1500 ML 2.689,43
G-NDK 18/11/2014 18/01/2015 KLATEN SURABAYA Aqua 1500 ML 2.689,43
G-NDK 18/11/2014 18/01/2015 KLATEN PASURUAN Aqua 1500 ML 3.046,94
G-NDK 18/11/2014 18/01/2015 KLATEN PROBOLINGGOAqua 1500 ML3.246,39
G-NDK 18/11/2014 18/01/2015 KLATEN TANGGUL Aqua 1500 ML 3.973,94
G-NDK 18/11/2014 18/01/2015 KLATEN SITUBONDO Aqua 1500 ML 3.709,26
G-NDK 18/11/2014 18/01/2015 KLATEN PURWOSARI Aqua 1500 ML 3.046,94
G-NDK 18/11/2014 18/01/2015 KLATEN SURABAYA Aqua 600 ML 2.186,42
G-NDK 18/11/2014 18/01/2015 KLATEN GRESIK Aqua 600 ML 2.186,42
G-NDK 18/11/2014 18/01/2015 KLATEN PASURUAN Aqua 600 ML 2.491,24
G-NDK 18/11/2014 18/01/2015 KLATEN TANGGUL Aqua 600 ML 3.154,41
G-NDK 18/11/2014 18/01/2015 KLATEN TANGGUL Aqua 600 ML 3.154,41
G-NDK 18/11/2014 18/01/2015 KLATEN PURWOSARI Aqua 600 ML 2.491,24
G-NDK 18/11/2014 18/01/2015 KLATEN PROBOLINGGO Aqua 600 ML 2.602,88
G-NDK 19/01/2015 31/03/2015 KLATEN GRESIK Aqua 1500 ML 2.554,96
G-NDK 19/01/2015 31/03/2015 KLATEN SURABAYA Aqua 1500 ML 2.554,96
G-NDK 19/01/2015 31/03/2015 KLATEN PASURUAN Aqua 1500 ML 2.894,59
G-NDK 19/01/2015 31/03/2015 KLATEN PROBOLINGGO Aqua 1500 ML 3.084,07
G-NDK 19/01/2015 31/03/2015 KLATEN TANGGUL Aqua 1500 ML 3.775,24
G-NDK 19/01/2015 31/03/2015 KLATEN SITUBONDO Aqua 1500 ML 3.709,26
G-NDK 19/01/2015 31/03/2015 KLATEN PURWOSARI Aqua 1500 ML 2.894,59
G-NDK 19/01/2015 31/03/2015 KLATEN SURABAYA Aqua 600 ML 2.077,10
G-NDK 19/01/2015 31/03/2015 KLATEN GRESIK Aqua 600 ML 2.077,10
G-NDK 19/01/2015 31/03/2015 KLATEN PASURUAN Aqua 600 ML 2.366,68
G-NDK 19/01/2015 31/03/2015 KLATEN TANGGUL Aqua 600 ML 2.996,69
G-NDK 19/01/2015 31/03/2015 KLATEN TANGGUL Aqua 600 ML 2.996,69
G-NDK 19/01/2015 31/03/2015 KLATEN PURWOSARI Aqua 600 ML 2.366,68

 

[Khalid NGO]

Hello Antonius and welcome to the forum

Please post your question in excel file, and mention your expected result.

Regards,

 

[Antonius]

Thank you, here is the sample file.

i'm Trying to fill the "Master Price" Column in Sheet1, based on the Parameter on Sheet1 compare to Sheet2.

 

[Nebu]

Hi:

Formula in yellow cells.

Thanks

 

[Khalid NGO]

Thanks for the file...

Try this in sheet1 F3:

=SUMPRODUCT((Sheet2!$A$3:$A$609=E3)*(Sheet2!$D$3:$D$609=C3)*(Sheet2!$E$3:$E$609=D3)*(Sheet2!$F$3:$F$609=B3)*(Sheet2!$B$3:$B$609<=A3)*(Sheet2!$C$3:$C$609>=A3)*(Sheet2!$G$3:$G$609))

Dates in column B (sheet1) are not in real numbers, i had converted them in real numbers by removing a leading space.

Regards,

Edit: Nebu again
Didn't noticed you replied.

 

[Khalid NGO]

With SUMIFS:

=SUMIFS(Sheet2!$G$3:$G$609,Sheet2!$A$3:$A$609,E3,Sheet2!$D$3:$D$609,C3,Sheet2!$E$3:$E$609,D3,Sheet2!$F$3:$F$609,B3,Sheet2!$B$3:$B$609,"<="&A3,Sheet2!$C$3:$C$609,">="&A3)

 

[Antonius]

Thank you Nebu, Khalid you both are the Greatest.
Thank You..Thank You, Thank You

 

[Khalid NGO]

Thank you Nebu, Khalid you both are the Greatest.
Thank You..Thank You, Thank You

You are welcome dear.
Thanks for the valued feedback.

Keep visiting , you'll become awesome in excel.

 

[Antonius]

already Try, both sumproduct from Nebu and Khalid, also Sumifs. and the result is the same, but processing time using Sumifs is faster than sumproduct

 

[Khalid NGO]

already Try, both sumproduct from Nebu and Khalid, also Sumifs. and the result is the same, but processing time using Sumifs is faster than sumproduct

Yes you are right.

SUMIF(S) are much faster than SumProduct.
but SUMIF(S) have their limitations, such as thy does not support mixed arrays (vertical + horizontal), they doesn't work with some date ranges arrays, while SUMPRODUCT does.
for more details visit:
http://stackoverflow.com/questions/27535531/sumproduct-vs-sumifs

Regards,

 

[Antonius]

thank you for the link, it's very helpfull for me to decide which formula is more suitable.