Sumproduct with And and OR

[DerekWooley]

Hello, I am using excel 2003 and would like to know if the below formula has any issues with it because I keep getting the #Value ! error and I'm not sure why.

Below is my formula.

I want the first two to be OR which is why there is a + sign between the. and the others are all * for AND.
[TABLE="width: 1632"]
<tbody>[TR]
[TD]=SUMPRODUCT(((Adjustments!$E$9:$E$10000="WIP")+(Adjustments!$E$9:$E$10000=B162))*(Adjustments!$B$9:$B$10000='Runout Calculation+Prev Run Qty'!B162)*(Adjustments!$N$9:$N$10000>L162)*(Adjustments!$N$9:$N$10000<>0)*Adjustments!$H$9:$H$10000)[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[sheetspread]

Either +0 or -- to convert the boolean to 0/1. Sumproduct with * and the simple + operation often do that but there are exceptions, like here perhaps.

 

[joeu2004]

I keep getting the #Value ! error and I'm not sure why.

My guess: some of the cells in Adjustments!H9:H10000 contain non-numeric values. Instead of multiplying the last term, try making it a separate parameter, to wit:

=SUMPRODUCT(((Adjustments!$E$9:$E$10000="WIP")+(Adjustments!$E$9:$E$10000=B162))
*(Adjustments!$B$9:$B$10000='Runout Calculation+Prev Run Qty'!B162)
*(Adjustments!$N$9:$N$10000>L162)*(Adjustments!$N$9:$N$10000<>0), Adjustments!$H$9:$H$10000)

Caveat: You might use semicolon (";") instead of comma (",") to separate parameters.

There is no need to prefix the first parameter with "--" (double negate) or "0+" (add zero).

Any arihmetic operation is sufficient to convert TRUE/FALSE values into 1/0. You are multiplying the boolean terms.

 

[Aladin Akyurek]

@ DerekWooley

The formula contains references to B162 and 'Runout Calculation+Prev Run Qty'!B162. Are these different references?

If they are same, the formula contains two ranges which must meet B162, i.e. Adjustments!$E$9:$E$10000 and Adjustments!$B$9:$B$10000.

Care to clarify?

 

[DerekWooley]

The reference for B162 is there twice because I wanted to first use the first column in my lookup table in column B and then look up the key word which is in column E. What I want the formula to do is first look at column B for the part , then look at the Key word being 30563 or "WIP", ( I also want to add "end" as an OR), then look for date not being 0 AND greater than L162 which is a date on my original sheet.

The formula should return the value of 5045 in the below lookup table.


Also, below is my original formula which worked great but I noticed that I need to look for 2 other key words (WIP and end) besides 5043 with an OR functionality in column E.
[TABLE="width: 1632"]
<tbody>[TR]
[TD]Original formula without multiple key words:
=SUMPRODUCT(--(Adjustments!$B$9:$B$10000='Runout Calculation+Prev Run Qty'!B162),--(Adjustments!$N$9:$N$10000>L162),--(Adjustments!$E$9:$E$10000="5043"),--(Adjustments!$N$9:$N$10000<>0),Adjustments!$H$9:$H$10000)[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


[TABLE="width: 319"]
<tbody>[TR]
[TD]B
[/TD]
[TD]E
[/TD]
[TD]H
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]Part #
[/TD]
[TD]Key Word
[/TD]
[TD]Quantity
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD="align: right"]30563
[/TD]
[TD="align: right"]30563
[/TD]
[TD="align: right"]1439
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30563
[/TD]
[TD="align: right"]30563
[/TD]
[TD="align: right"]5045
[/TD]
[TD="align: right"]6/6/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]30563
[/TD]
[TD]Reservoir
[/TD]
[TD="align: right"]6484
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30563
[/TD]
[TD="align: right"]330
[/TD]
[TD="align: right"]172
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30563
[/TD]
[TD]Adj
[/TD]
[TD="align: right"]-16.65
[/TD]
[TD="align: right"]3/16/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]30563
[/TD]
[TD]Elect.
[/TD]
[TD="align: right"]4430.44
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30563
[/TD]
[TD="align: right"]30563
[/TD]
[TD="align: right"]-150
[/TD]
[TD="align: right"]6/6/2018
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Just for the record:

B162 = 30563

L162 = What is the value of this cell?

 

[DerekWooley]

Just for the record:

B162 = 30563

L162 = What is the value of this cell?

Correct, B162 = 30563. I first want the formula to look at column B in the adjustments sheet and then it looks at column E for one of the 3 keywords (30563, "end", or "wip").

L162= 5/25/2018. in the table, the value of 5043 has the date 6/6/2018 and the keyword 30563 so that should be the only value that is returned.

 

[Aladin Akyurek]

Correct, B162 = 30563. I first want the formula to look at column B in the adjustments sheet and then it looks at column E for one of the 3 keywords (30563, "end", or "wip").

L162= 5/25/2018. in the table, the value of 5043 has the date 6/6/2018 and the keyword 30563 so that should be the only value that is returned.

Thanks.

In a convenient cell of Runout Calculation+Prev Run Qty, control+shift+enter, not just enter:


=IFERROR(INDEX(Adjustments!$H$2:$H$8,MATCH(1,IF(Adjustments!$B$2:$B$8=B162,IF(ISNUMBER(MATCH(Adjustments!$E$2:$E$8,CHOOSE({1,2,3},"wip","end",B162),0)),IF(ISNUMBER(Adjustments!$N$2:$N$8),IF(Adjustments!$N$2:$N$8>=L162,1)))),0)),"not available")

 

[DerekWooley]

Thanks.

In a convenient cell of Runout Calculation+Prev Run Qty, control+shift+enter, not just enter:


=IFERROR(INDEX(Adjustments!$H$2:$H$8,MATCH(1,IF(Adjustments!$B$2:$B$8=B162,IF(ISNUMBER(MATCH(Adjustments!$E$2:$E$8,CHOOSE({1,2,3},"wip","end",B162),0)),IF(ISNUMBER(Adjustments!$N$2:$N$8),IF(Adjustments!$N$2:$N$8>=L162,1)))),0)),"not available")

Does this formula only take the first instance or does it add? When I used it, I only got the first instance where I saw what was in B162. There are other instances that have the word wip and end that were not added. I want to be able to combine all the quantities in column H where it says either wip, end, or value in b162.

 

[Aladin Akyurek]

Does this formula only take the first instance or does it add? When I used it, I only got the first instance where I saw what was in B162. There are other instances that have the word wip and end that were not added. I want to be able to combine all the quantities in column H where it says either wip, end, or value in b162.

Control+shift+enter, not just enter:

=SUM(IF(Adjustments!$B$2:$B$8=B162,IF(ISNUMBER(MATCH(Adjustments!$E$2:$E$8,CHOOSE({1,2,3},"wip","end",B162),0)),IF(ISNUMBER(Adjustments!$N$2:$N$8),IF(Adjustments!$N$2:$N$8>=L162,Adjustments!$H$2:$H$8)))))