SUMPRODUCT(IF()) and AVERAGE(IF())

[TG2812]

Hello All,

Lately, I have been trying to get my around the following issues without much success. What I want to do is the following:

Column C (sheet1): Based on multiple conditions, I would like to retrieve the number of units per model (sheet 1 column B) that are found in sheet 3 column A but not found in sheet 4 column A using their serial number (sheet 3 column B and sheet 4 column B).

Column D (sheet 1): I would like to do exactly the same thing than above but the formula should include another condition. I need to retrieve the number of units found in sheet 3 but not in sheet 4 which dates are greater than 6 months old)

Column E (sheet 1) is the most difficult part: if serial numbers are found in both sheet 3 and sheet 4, the formula should subtract the date and average the result of all subtractions.

Please note that I work with arrays of various dimensions.
I thank you very much in advance for your time and help and looking forward to learning from your solutions.

 

[Aladin Akyurek]

Try to post 5 rows from column C of Sheet1, column B of Sheet1, column A of Sheet3, and column B of Sheet4 one following the other...

 

[TG2812]

Hello Aladin Akyurek,


Sheet 1

Col B Col C Col D Col E
Row 3 Model Family Number of units not found Number of units not found > 6 mths

 

[oldbrewer]

copy 5 rows and paste into reply box

 

[Aladin Akyurek]

copy 5 rows and paste into reply box

from each sheet, one after another.

 

[TG2812]

Try to post 5 rows from column C of Sheet1, column B of Sheet1, column A of Sheet3, and column B of Sheet4 one following the other...

Sheet 1

	B	C	D	E
row 3	Model Family	Number of units not found	Number of units not found > 6 mths	Average time
row 4	model 1
row 5	model 2
row 6	model 2
row 7	model 3
row 8	model 3

<tbody>
</tbody>

Sheet 3

	A	B	C	D
row 1	Model Family	Model Number	Order Number	Date 1
row 2	model 1	F00275	QHWJ00	01/06/2016
row 3	model 1	F00238	RFYD00	01/22/2016
row 4	model 1	F00467	WTSY00	07/26/2016
row 5	model 1	F00465	WTSX00	07/26/2016
row 6	model 3	Y10182	BXDS00	08/10/2016
row 7	model 3	Y00186	VMVK00	08/10/2016
row 8	model 2	G00189	LFWV00	02/22/2017
row 9	model 2	G00191	LKNL00	02/24/2017

<tbody>
</tbody>

Sheet 4

	A	B	C	D	E
row 1	Model Family	Qty	Model Number	Order number	Date 2
row 2	model 1	1	F00231	LLXX00	2017/03/30
row 3	model 1	1	F00238	RFYD00	2017/02/25
row 4	model 1	1	F00275	QHWJ00	2017/03/13
row 5	model 3	1	Y10182	BXDS00	2017/03/29
row 6	model 3	1	Y10187	BXDT00	2017/03/29

<tbody>
</tbody>

 

[TG2812]

Hello oldbrewer,

I tried to reproduce my worksheet but this is not that easy. Hope you can reproduce it into your Excel.
Let me know if you need anything else from my side.
Many thanks.

 

[Aladin Akyurek]

What is the expected value in C4 of Sheet1, given the samples you posted?

 

[TG2812]

What is the expected value in C4 of Sheet1, given the samples you posted?

the formula in C4 should return 2 (F00467 and F00465) = > found in sheet 3 but not in sheet 4
the formula in D4 should return 2 (F00467 and F00465) > 07/26/2016 is greater than today - 180 days (6 months old)


the formula in E4 should return 416 => for all model number found in both sheet (F00238 and F00275 for instance for the model 1), the formula is supposed to subtract its date in sheet 4 from its date in sheet 3 like the following:

F00238 => 2017/02/25 - 01/22/2016 = 432
F00275 => 2017/03/13 - 01/06/2016 = 400

Average of 432 and 400 = 416

 

[TG2812]

Does it help Aladin Akyurek?