Roger,

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

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

"Kathy" wrote in message
...
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

"Kathy" wrote:

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.