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]

This is what the final version looks like (in C4): =SUM(IF(ISNA(MATCH(IF(TRIM(Sheet3!$G$2:$G$856)=Sheet1!B4,IF(MID(TRIM(Sheet3!$A$2:$A$856),SEARCH("DCA",TRIM(Sheet3!$A$2:$A$856)),4)=MID(Sheet1!C4,SEARCH("DCA",Sheet1!C4),4),Sheet3!$C$2:$C$856)),IF(TRIM(Sheet3!$G$2:$G$856)=Sheet1!B4,IF(MID(TRIM(Sheet3!$A$2:$A$856),SEARCH("DCA",TRIM(Sheet3!$A$2:$A$856)),4)=MID(Sheet1!C4,SEARCH("DCA",Sheet1!C4),4),Sheet4!$K$2:$K$1500)),0)),1))

Delete "Sheet1!" from the formula for reasons of readability.

[/quote]Do you have any idea how I could solve issue 3?[/QUOTE]

In E4 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(ISNUMBER(MATCH(IF(Sheet3!$A$2:$A$9=$B4,Sheet3!$B$2:$B$9),IF(Sheet4!$A$2:$A$6=$B4,Sheet4!$C$2:$C$6),0)),IF(ISNUMBER(Sheet4!$E$2:$E$6-Sheet3!$D$2:$D$9),Sheet4!$E$2:$E$6-Sheet3!$D$2:$D$9)))

This pertains to the original samples you posted here at my request...

 

[TG2812]

Delete "Sheet1!" from the formula for reasons of readability.

Do you have any idea how I could solve issue 3?[/QUOTE]

In E4 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(ISNUMBER(MATCH(IF(Sheet3!$A$2:$A$9=$B4,Sheet3!$B$2:$B$9),IF(Sheet4!$A$2:$A$6=$B4,Sheet4!$C$2:$C$6),0)),IF(ISNUMBER(Sheet4!$E$2:$E$6-Sheet3!$D$2:$D$9),Sheet4!$E$2:$E$6-Sheet3!$D$2:$D$9)))

This pertains to the original samples you posted here at my request...[/QUOTE]

Thank you Aladin Akyurek.
When I try to adapt it to my original file, it does not return me the right result. Should the arrays dimension when doing the difference be identical in size?

 

[TG2812]

Hello Aladin Akyurek,

Seems that the formula I adapted to my original file does not work. Particularly the second part when we do the differences between the two dates column.
Normally when an model number is found in both worksheet, it should look up the current row for these model number, keep it in memory and then average all differences...