Sumproduct with multiple criteria
 

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.

Sumproduct with multiple criteria
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

Sumproduct with multiple criteria
 

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.


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

Sumproduct with multiple criteria
 

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


--
Regards
Roger Govier

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

Sumproduct with multiple criteria
 

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.

Sumproduct with multiple criteria
 

We can make a SUMPRODUCT formula since in the part

I presume you mean: "We can not make" as you explain later that the range
are of different size.

'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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

Sumproduct with multiple criteria
 

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.

Sumproduct with multiple criteria
 

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.

Sumproduct with multiple criteria
 

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

--
Regards
Roger Govier

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