Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with multiple criteria | Excel Worksheet Functions | |||
Sumproduct - Multiple Criteria | Excel Discussion (Misc queries) | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
SumProduct With Multiple criteria | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions |