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
[TABLE="width: 289"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]row 3
[/TD]
[TD] Model Family
[/TD]
[TD]Number of units not found
[/TD]
[TD]Number of units not found > 6 mths
[/TD]
[TD] Average time
[/TD]
[/TR]
[TR]
[TD]row 4
[/TD]
[TD]model 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 5
[/TD]
[TD]model 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 6
[/TD]
[TD]model 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 7
[/TD]
[TD]model 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 8
[/TD]
[TD]model 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 3

[TABLE="width: 38"]
<tbody>[TR]
[TD] [/TD]
[TD] A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]row 1
[/TD]
[TD]Model Family
[/TD]
[TD]Model Number
[/TD]
[TD]Order Number
[/TD]
[TD] Date 1
[/TD]
[/TR]
[TR]
[TD]row 2
[/TD]
[TD]model 1
[/TD]
[TD]F00275
[/TD]
[TD]QHWJ00
[/TD]
[TD]01/06/2016
[/TD]
[/TR]
[TR]
[TD]row 3
[/TD]
[TD]model 1
[/TD]
[TD]F00238
[/TD]
[TD]RFYD00
[/TD]
[TD]01/22/2016
[/TD]
[/TR]
[TR]
[TD]row 4
[/TD]
[TD]model 1
[/TD]
[TD]F00467
[/TD]
[TD]WTSY00
[/TD]
[TD]07/26/2016
[/TD]
[/TR]
[TR]
[TD]row 5
[/TD]
[TD]model 1
[/TD]
[TD]F00465
[/TD]
[TD]WTSX00
[/TD]
[TD]07/26/2016
[/TD]
[/TR]
[TR]
[TD]row 6
[/TD]
[TD]model 3
[/TD]
[TD]Y10182
[/TD]
[TD]BXDS00
[/TD]
[TD]08/10/2016
[/TD]
[/TR]
[TR]
[TD]row 7
[/TD]
[TD]model 3
[/TD]
[TD]Y00186
[/TD]
[TD]VMVK00
[/TD]
[TD]08/10/2016
[/TD]
[/TR]
[TR]
[TD]row 8
[/TD]
[TD]model 2
[/TD]
[TD]G00189
[/TD]
[TD]LFWV00
[/TD]
[TD]02/22/2017
[/TD]
[/TR]
[TR]
[TD]row 9
[/TD]
[TD]model 2
[/TD]
[TD]G00191
[/TD]
[TD]LKNL00
[/TD]
[TD]02/24/2017
[/TD]
[/TR]
</tbody>[/TABLE]








Sheet 4
[TABLE="width: 38"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]row 1
[/TD]
[TD]Model Family
[/TD]
[TD] Qty
[/TD]
[TD]Model Number
[/TD]
[TD]Order number
[/TD]
[TD]Date 2
[/TD]
[/TR]
[TR]
[TD]row 2
[/TD]
[TD]model 1
[/TD]
[TD]1
[/TD]
[TD]F00231
[/TD]
[TD]LLXX00
[/TD]
[TD]2017/03/30
[/TD]
[/TR]
[TR]
[TD]row 3
[/TD]
[TD]model 1
[/TD]
[TD]1
[/TD]
[TD]F00238
[/TD]
[TD]RFYD00
[/TD]
[TD]2017/02/25
[/TD]
[/TR]
[TR]
[TD]row 4
[/TD]
[TD]model 1
[/TD]
[TD]1
[/TD]
[TD]F00275
[/TD]
[TD]QHWJ00
[/TD]
[TD]2017/03/13
[/TD]
[/TR]
[TR]
[TD]row 5
[/TD]
[TD]model 3
[/TD]
[TD]1
[/TD]
[TD]Y10182
[/TD]
[TD]BXDS00
[/TD]
[TD]2017/03/29
[/TD]
[/TR]
[TR]
[TD]row 6
[/TD]
[TD]model 3
[/TD]
[TD]1
[/TD]
[TD]Y10187
[/TD]
[TD]BXDT00
[/TD]
[TD]2017/03/29
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?