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.

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

<tbody>
</tbody><colgroup><col></colgroup>

 

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

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)

<tbody>
</tbody><colgroup><col></colgroup>

B	E	H	N
Part #	Key Word	Quantity	Date
30563	30563	1439
30563	30563	5045	6/6/2018
30563	Reservoir	6484
30563	330	172
30563	Adj	-16.65	3/16/2018
30563	Elect.	4430.44
30563	30563	-150	6/6/2018

<tbody>
</tbody>

 

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