Sumproduct with multiple criteria

[Kathy]

I cannot get this formula to work correctly.
=SUMIFS('Material needed for open orders'!$J$3:$J$445,'Material needed for
open orders'!$A$3:$A$445,"<>&'Raw Material Planning'!$B$2:$B$132",'Material
needed for open orders'!$C$3:$C$445,"<='Material needed for open
orders'!$N$1",'Material needed for open orders'!$I$3:$I$445,'Raw Material
Planning'!Y$2))

I am trying to sum the material requirements ('Material needed for open
orders'!$J$3:$J$445) where the finished part number does not match a
pre-determined list ('Material needed for open orders'!$A$3:$A$445,"<>'Raw
Material Planning'!$B$2:$B$132"), is less than or equal a given date (
'Material needed for open orders'!$C$3:$C$445,"<='Material needed for open
orders'!$N$1") where the material required matches a specific material
('Material needed for open orders'!$I$3:$I$445,'Raw Material Planning'!Y$2).
I can make some of the pieces of the statement work correctly with the
exception of not matching the pre-determined list of finish part numbers.

 

[Bernard Liengme]

We can make a SUMPRODUCT formula since in the part

'Material needed for open orders'!$A$3:$A$445<>'Raw Material
Planning'!$B$2:$B$132

You are trying to compare 443 values ($A$3:$A$445) with 131 values
$B$2:$B$132


It might help if you gave us a better verbal description of the problem
best wishes

 

[Khoshravan]

I recommend to use very short name for your sheets. It is very hard to read
your formula.Use A for one sheet and B for the other. It is not necessary
that sheet names to be meaningful.

 

[Roger Govier]

Hi Kathy

You need to use "<>" & - not put the closing double quote after the
reference.
Also, all of your ranges need to be of equal length.

=SUMIFS('Material needed for open orders'!$J$3:$J$445,
'Material needed for open orders'!$A$3:$A$445,
"<>"&'Raw Material Planning'!$B$3:$B$445,
'Material needed for open orders'!$C$3:$C$445,
"<=" &'Material needed for open orders'!$N$1,
'Material needed for open orders'!$I$3:$I$445,
'Raw Material Planning'!Y$2))

 

[Kathy]

My apologies for the lengthy names, I just used copy and paste.
I am trying to sum the material requirements ('Sheet C'!$J$3:$J$445) where
the finished part number does not match a pre-determined list (Sheet
C'!$A$3:$A$445,"<>'Sheet A'!$B$2:$B$132"), is less than or equal a given date
('Sheet C'!$C$3:$C$445,"<=Sheet C'!$N$1") and where the material required
matches a specific material (Sheet C'!$I$3:$I$445,'Sheet A'!Y$2).
I have tried the moving the quotation marks and the problem still persists.
It may be that I am trying to compare different size ranges, but I do have a
list of values that are 443 lines long and I need to pull items out of that
list that do not match items in a list that is only 121 lines. The number of
lines will change each time we update the worksheet.

Thank you

 

[Khoshravan]

We can make a SUMPRODUCT formula since in the partI presume you mean: "We can not make" as you explain later that the range
are of different size.

 

[Roger Govier]

Hi Kathy

If your lists cannot be of equal length, then perhaps this will suit.

=SUMPRODUCT(
--(NOT(ISNUMBER(MATCH(
SheetC!$A$3:$A$445,SheetA!$B$2:$B$132,0)))),
--(SheetC!$C$3:$C$445,<=SheetC!$N$1),
--(SheetC!$I$3:$I$445=SheetA!Y$2),
SheetC!$J$3:$J$445)

The formula should of course be all on one line, but I have deliberately
broken it to try and prevent the newsreader breaking it in funny positions.

Because we are testing each of the values in A3:A445 for a MATCH, the range
against which they have to match does not have to be of equal length.

 

[Kathy]

Roger,

This formula gives me #N/A, when I know it should be returning some number.
Do you have any other ideas?

 

[Roger Govier]

Hi Kathy

Do you want to mail me the workbook, and I will take a look. The formula
worked fine for me.
To send direct
roger at technology4u dot co dot uk
Change the at and dots to make valid email address