Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct or lookup
I have a table of data for multiple types of products manufactured over a
period of time. X Y Z Product a b c ... 5 2 3 0 8 11 I am grouping these products in another area A B C Group 1 Group 2 Group 3 a b d c e g ... What I want to do is based on the group look up the product and some the number of batches made. I am trying to do a sumproduct(--(A2:A8=X1:X10),(X2:X10)) however this returms a #VALUE!. I am assuming because I am trying to search for multiple products instead of 1. Any help would be great. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct or lookup
The main problem I see is that your ranges don't match up.
Your firt range is A2:A8, which is a total of seven cells. Your second range is X1:X10 (a total of 10 cells). Then your third range is a total of 9 cells. Note: Your ranges don't necessarily have to be the same exact rows, but the number of cells in each range need to be the same. May be it should be: =SUMPRODUCT(--(A2:A10=X2:X10),(X2:X10)) HTH, Paul -- "vito" wrote in message ... I have a table of data for multiple types of products manufactured over a period of time. X Y Z Product a b c ... 5 2 3 0 8 11 I am grouping these products in another area A B C Group 1 Group 2 Group 3 a b d c e g ... What I want to do is based on the group look up the product and some the number of batches made. I am trying to do a sumproduct(--(A2:A8=X1:X10),(X2:X10)) however this returms a #VALUE!. I am assuming because I am trying to search for multiple products instead of 1. Any help would be great. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct or lookup
The range I am looking in contains 25 types of product. From those 25 I will
reference 25 values representing the number of batches for each product. The number of products in a group i want to look at is 17. The exact formula I am trying to use is; SUMPRODUCT(--($H$65:$H$67=$B$38:$D$38),(B39:D39)) Group Product List # of batches When i look for a single product by changing the formula to; SUMPRODUCT(--($H$65=$B$38:$D$38),(B39:D39)) it works. I just can't seem to get sumproduct to sum multiple values for a multiple reference. "PCLIVE" wrote: The main problem I see is that your ranges don't match up. Your firt range is A2:A8, which is a total of seven cells. Your second range is X1:X10 (a total of 10 cells). Then your third range is a total of 9 cells. Note: Your ranges don't necessarily have to be the same exact rows, but the number of cells in each range need to be the same. May be it should be: =SUMPRODUCT(--(A2:A10=X2:X10),(X2:X10)) HTH, Paul -- "vito" wrote in message ... I have a table of data for multiple types of products manufactured over a period of time. X Y Z Product a b c ... 5 2 3 0 8 11 I am grouping these products in another area A B C Group 1 Group 2 Group 3 a b d c e g ... What I want to do is based on the group look up the product and some the number of batches made. I am trying to do a sumproduct(--(A2:A8=X1:X10),(X2:X10)) however this returms a #VALUE!. I am assuming because I am trying to search for multiple products instead of 1. Any help would be great. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct or lookup
Sorry copied the wrong formula;
=SUMPRODUCT(--($H$65:$H$81=$B$38:$Z$38),(B39:Z39)) Group of products All products # of batches works with SUMPRODUCT(--($H$65=$B$38:$Z$38),(B39:Z39)) Single product All products # of batches "vito" wrote: The range I am looking in contains 25 types of product. From those 25 I will reference 25 values representing the number of batches for each product. The number of products in a group i want to look at is 17. The exact formula I am trying to use is; SUMPRODUCT(--($H$65:$H$67=$B$38:$D$38),(B39:D39)) Group Product List # of batches When i look for a single product by changing the formula to; SUMPRODUCT(--($H$65=$B$38:$D$38),(B39:D39)) it works. I just can't seem to get sumproduct to sum multiple values for a multiple reference. "PCLIVE" wrote: The main problem I see is that your ranges don't match up. Your firt range is A2:A8, which is a total of seven cells. Your second range is X1:X10 (a total of 10 cells). Then your third range is a total of 9 cells. Note: Your ranges don't necessarily have to be the same exact rows, but the number of cells in each range need to be the same. May be it should be: =SUMPRODUCT(--(A2:A10=X2:X10),(X2:X10)) HTH, Paul -- "vito" wrote in message ... I have a table of data for multiple types of products manufactured over a period of time. X Y Z Product a b c ... 5 2 3 0 8 11 I am grouping these products in another area A B C Group 1 Group 2 Group 3 a b d c e g ... What I want to do is based on the group look up the product and some the number of batches made. I am trying to do a sumproduct(--(A2:A8=X1:X10),(X2:X10)) however this returms a #VALUE!. I am assuming because I am trying to search for multiple products instead of 1. Any help would be great. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct or lookup
Vito,
Your sumproduct ranges have to be single columns and they all have to have the same number of cells. You can't do B38:Z38 - trying for a horizontal array. Even if you rearanged the data, the number of cells in h65:h81 is not the same as the number of cells in b38:z38. This approach won't work. "vito" wrote: Sorry copied the wrong formula; =SUMPRODUCT(--($H$65:$H$81=$B$38:$Z$38),(B39:Z39)) Group of products All products # of batches works with SUMPRODUCT(--($H$65=$B$38:$Z$38),(B39:Z39)) Single product All products # of batches "vito" wrote: The range I am looking in contains 25 types of product. From those 25 I will reference 25 values representing the number of batches for each product. The number of products in a group i want to look at is 17. The exact formula I am trying to use is; SUMPRODUCT(--($H$65:$H$67=$B$38:$D$38),(B39:D39)) Group Product List # of batches When i look for a single product by changing the formula to; SUMPRODUCT(--($H$65=$B$38:$D$38),(B39:D39)) it works. I just can't seem to get sumproduct to sum multiple values for a multiple reference. "PCLIVE" wrote: The main problem I see is that your ranges don't match up. Your firt range is A2:A8, which is a total of seven cells. Your second range is X1:X10 (a total of 10 cells). Then your third range is a total of 9 cells. Note: Your ranges don't necessarily have to be the same exact rows, but the number of cells in each range need to be the same. May be it should be: =SUMPRODUCT(--(A2:A10=X2:X10),(X2:X10)) HTH, Paul -- "vito" wrote in message ... I have a table of data for multiple types of products manufactured over a period of time. X Y Z Product a b c ... 5 2 3 0 8 11 I am grouping these products in another area A B C Group 1 Group 2 Group 3 a b d c e g ... What I want to do is based on the group look up the product and some the number of batches made. I am trying to do a sumproduct(--(A2:A8=X1:X10),(X2:X10)) however this returms a #VALUE!. I am assuming because I am trying to search for multiple products instead of 1. Any help would be great. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct or lookup
Vito,
Last post contained a big error. Sorry. You can use horizontal arrays but you can't mix horizontal and vertical arrays. Jim "vito" wrote: Sorry copied the wrong formula; =SUMPRODUCT(--($H$65:$H$81=$B$38:$Z$38),(B39:Z39)) Group of products All products # of batches works with SUMPRODUCT(--($H$65=$B$38:$Z$38),(B39:Z39)) Single product All products # of batches "vito" wrote: The range I am looking in contains 25 types of product. From those 25 I will reference 25 values representing the number of batches for each product. The number of products in a group i want to look at is 17. The exact formula I am trying to use is; SUMPRODUCT(--($H$65:$H$67=$B$38:$D$38),(B39:D39)) Group Product List # of batches When i look for a single product by changing the formula to; SUMPRODUCT(--($H$65=$B$38:$D$38),(B39:D39)) it works. I just can't seem to get sumproduct to sum multiple values for a multiple reference. "PCLIVE" wrote: The main problem I see is that your ranges don't match up. Your firt range is A2:A8, which is a total of seven cells. Your second range is X1:X10 (a total of 10 cells). Then your third range is a total of 9 cells. Note: Your ranges don't necessarily have to be the same exact rows, but the number of cells in each range need to be the same. May be it should be: =SUMPRODUCT(--(A2:A10=X2:X10),(X2:X10)) HTH, Paul -- "vito" wrote in message ... I have a table of data for multiple types of products manufactured over a period of time. X Y Z Product a b c ... 5 2 3 0 8 11 I am grouping these products in another area A B C Group 1 Group 2 Group 3 a b d c e g ... What I want to do is based on the group look up the product and some the number of batches made. I am trying to do a sumproduct(--(A2:A8=X1:X10),(X2:X10)) however this returms a #VALUE!. I am assuming because I am trying to search for multiple products instead of 1. Any help would be great. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup and sumproduct | Excel Discussion (Misc queries) | |||
maybe lookup/index/match/sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct and horizontal lookup | Excel Discussion (Misc queries) | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
from sumproduct to a lookup please | Excel Worksheet Functions |