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]

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

Does it help Aladin Akyurek?

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

=SUM(IF(ISNA(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)),1))

This assumes that a model number occurs in a sheet just once at any time.

Involving the second question: Does this 6 month condition must hold just for just date1?

 

[TG2812]

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

=SUM(IF(ISNA(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)),1))

This assumes that a model number occurs in a sheet just once at any time.

Involving the second question: Does this 6 month condition must hold just for just date1?

Thanks Aladin Akyurek but the formula is not fully working. For model 1 (in cells C4), the formula retuens the right result. However when you drag it down, it doesn't return the right result for the model 2 (no model number for model 2 contained in sheet 3 are found in sheet 4...)


For the second question, the 6 month condition only holds for date 1. You are correct. Example. If the model number in sheet 3 is not found in sheet 4 and the date of this model number in sheet 3 is older than 6 month old, then the formula should count 1.

 

[Aladin Akyurek]

Thanks Aladin Akyurek but the formula is not fully working. For model 1 (in cells C4), the formula retuens the right result. However when you drag it down, it doesn't return the right result for the model 2 (no model number for model 2 contained in sheet 3 are found in sheet 4...)

[...]

Are you wanting this...

=SUM(IF(ISNA(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)),1))

or this

=SUM(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)),1))

in C4?

Recall applying control+shift+enter to these formulas.

The former checks for the absence of model numbers from Sheet3 in Sheet4, the latter does the opposite, i.e. checks for presence.

 

[TG2812]

Are you wanting this...

=SUM(IF(ISNA(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)),1))

or this

=SUM(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)),1))

in C4?

Recall applying control+shift+enter to these formulas.

The former checks for the absence of model numbers from Sheet3 in Sheet4, the latter does the opposite, i.e. checks for presence.

I, indeed, need the first formula. However, what does it return you for Model 2? It returns me 2 while it should be 0..
Another question regarding formula in C4: can I add other conditions before the first IF? Example =SUM(IF(myothercondition,IF(ISNA(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)),1))

 

[Aladin Akyurek]

I, indeed, need the first formula. However, what does it return you for Model 2? It returns me 2 while it should be 0..
Another question regarding formula in C4: can I add other conditions before the first IF? Example =SUM(IF(myothercondition,IF(ISNA(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)),1))

Formula 1 delivers indeed 2 because

"G00189";"G00191"

associated with model 2 are not in Sheet4.

 

[TG2812]

Formula 1 delivers indeed 2 because

"G00189";"G00191"

associated with model 2 are not in Sheet4.

Aladin Akyurek you are correct. My formula in the end looks like this. In my original file, I have some other conditions to include:

=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))

Still does not return me the expected result..=(

 

[TG2812]

Aladin Akyurek, I basically added some more condition upon the checking of the model following your logic. However, the result is still not right.

 

[TG2812]

Aladin Akyurek, I basically added some more condition upon the checking of the model following your logic. However, the result is still not right.

Aladin Akyurek It now works!!!! Thank you very much!!!

Does anybody have a solution of the issue 3? Average(if())

 

[Aladin Akyurek]

Aladin Akyurek It now works!!!! Thank you very much!!!

Does anybody have a solution of the issue 3? Average(if())

Care to post the final version and indicate where it is implemented?

 

[TG2812]

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

Do you have any idea how I could solve issue 3?