If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumproduct help
First I want to say thank you for all the help everyone is here. Now for my
question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#2
|
|||
|
|||
sumproduct help
Try something like this:
=SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#3
|
|||
|
|||
sumproduct help
Hi Biff, This is what I got from what you said, I must have done something
wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#4
|
|||
|
|||
sumproduct help
Is this what your data looks like:
...........A..........B..........C 1......Type....Size........Qty 2........1........4x4..........2 3........1........4x8..........1 4........1........4x4..........4 5........2........4x8..........5 For Type 1, based on my understanding of your explanation the result should be 4. =SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2)) Result = 4 -- Biff Microsoft Excel MVP "kristap" wrote in message ... Hi Biff, This is what I got from what you said, I must have done something wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#5
|
|||
|
|||
sumproduct help
"kristap" wrote:
there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them. Try the following array formula (commit with ctrl-shift-Enter, not just Enter; Excel should enclose the formula with curly braces "{...}"): =SUMPRODUCT(VLOOKUP($B$3, Table!$A$1:$E$5, MATCH(Beltman!E12:E21, Table!$A$1:$E$1,0),0), Beltman!A12:A21) This assumes that in A1:E5 in a worksheet named Table (of course, you can customize all of that), you have a table of the following form: 1. A2:A5 contains the sizes of all pieces, e.g. 1x8, 2x8, 4x8 and 4x4. 2. B1:E3 also contains the same sizes of all pieces. I don't think the order matters, but I would put them in the same order as #1. 3. Each cell in the table contains the number of each piece in column A that can be made from the piece in row 1. For example, B2:E2 (1x8 piece) would contain 1, 2, 4, 0. The MATCH function determines the column to use in the table. The VLOOKUP function determines the row to use in the table. The SUMPRODUCT multiplies the number of pieces in stock (A12:A21) by the number of B3 pieces that can made from them (E12:E21). (Eventually, someone might be able to provide an equivalent non-array formula.) ----- original message ----- "kristap" wrote in message ... Hi Biff, This is what I got from what you said, I must have done something wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#6
|
|||
|
|||
sumproduct help
Hi Biff, Sorry for the confusion. Lets see if I can explain this better....
Each warehouse is set up as its own sheet. And its set up to look like this. .......A...........B.......C....D......E.........F ......G............................etc count hold wo qc type sku size 1 4 xps 4x4 2 23 iso 1x8 3 56 xps 4x8 4 4 xps 1x8 5 34 xps 4x8 6 23 xps 2x4 They have a quantity report that they have been manually figuring and entering the totals of each type into, and my husbands boss wants him to have it automatically do it for each of their warehouses. The report is set up like this (I will only do it for 1 type instead of all the columns for all the types) .........A................B........C........D..... ...... 2 3 warehouse XPS 4 Good Bad Hold 5 Beltman ? ? ? 6 They want to be able to look at this report and tell what each warehouse has in it. So in the example, I would need how many good xps were at the beltman warehouse. (A1:A6)..... But they want anything that is ?x4(4x4, 1x4 etc) to be divided by 2 because they can fit 2 pallets in the 4x8 space...need to also include in everything that is xps no matter what size......Hope this makes sense. "T. Valko" wrote: Is this what your data looks like: ...........A..........B..........C 1......Type....Size........Qty 2........1........4x4..........2 3........1........4x8..........1 4........1........4x4..........4 5........2........4x8..........5 For Type 1, based on my understanding of your explanation the result should be 4. =SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2)) Result = 4 -- Biff Microsoft Excel MVP "kristap" wrote in message ... Hi Biff, This is what I got from what you said, I must have done something wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#7
|
|||
|
|||
sumproduct help
Ok, I think I understand now...
For Type xps... Any size x8 = 1 Any size x4 = 1/2 =SUMPRODUCT(--(Type="xps"),((ISNUMBER(SEARCH("x8",Size)))*Count) +((ISNUMBER(SEARCH("x4",Size)))*Count/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message news Hi Biff, Sorry for the confusion. Lets see if I can explain this better.... Each warehouse is set up as its own sheet. And its set up to look like this. ......A...........B.......C....D......E.........F. .....G............................etc count hold wo qc type sku size 1 4 xps 4x4 2 23 iso 1x8 3 56 xps 4x8 4 4 xps 1x8 5 34 xps 4x8 6 23 xps 2x4 They have a quantity report that they have been manually figuring and entering the totals of each type into, and my husbands boss wants him to have it automatically do it for each of their warehouses. The report is set up like this (I will only do it for 1 type instead of all the columns for all the types) ........A................B........C........D...... ..... 2 3 warehouse XPS 4 Good Bad Hold 5 Beltman ? ? ? 6 They want to be able to look at this report and tell what each warehouse has in it. So in the example, I would need how many good xps were at the beltman warehouse. (A1:A6)..... But they want anything that is ?x4(4x4, 1x4 etc) to be divided by 2 because they can fit 2 pallets in the 4x8 space...need to also include in everything that is xps no matter what size......Hope this makes sense. "T. Valko" wrote: Is this what your data looks like: ...........A..........B..........C 1......Type....Size........Qty 2........1........4x4..........2 3........1........4x8..........1 4........1........4x4..........4 5........2........4x8..........5 For Type 1, based on my understanding of your explanation the result should be 4. =SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2)) Result = 4 -- Biff Microsoft Excel MVP "kristap" wrote in message ... Hi Biff, This is what I got from what you said, I must have done something wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#8
|
|||
|
|||
sumproduct help
We can shorten that a few keystrokes:
=SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="x8")*Count)+((RIGHT( Size,2)="x4")*Count/2)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, I think I understand now... For Type xps... Any size x8 = 1 Any size x4 = 1/2 =SUMPRODUCT(--(Type="xps"),((ISNUMBER(SEARCH("x8",Size)))*Count) +((ISNUMBER(SEARCH("x4",Size)))*Count/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message news Hi Biff, Sorry for the confusion. Lets see if I can explain this better.... Each warehouse is set up as its own sheet. And its set up to look like this. ......A...........B.......C....D......E.........F. .....G............................etc count hold wo qc type sku size 1 4 xps 4x4 2 23 iso 1x8 3 56 xps 4x8 4 4 xps 1x8 5 34 xps 4x8 6 23 xps 2x4 They have a quantity report that they have been manually figuring and entering the totals of each type into, and my husbands boss wants him to have it automatically do it for each of their warehouses. The report is set up like this (I will only do it for 1 type instead of all the columns for all the types) ........A................B........C........D...... ..... 2 3 warehouse XPS 4 Good Bad Hold 5 Beltman ? ? ? 6 They want to be able to look at this report and tell what each warehouse has in it. So in the example, I would need how many good xps were at the beltman warehouse. (A1:A6)..... But they want anything that is ?x4(4x4, 1x4 etc) to be divided by 2 because they can fit 2 pallets in the 4x8 space...need to also include in everything that is xps no matter what size......Hope this makes sense. "T. Valko" wrote: Is this what your data looks like: ...........A..........B..........C 1......Type....Size........Qty 2........1........4x4..........2 3........1........4x8..........1 4........1........4x4..........4 5........2........4x8..........5 For Type 1, based on my understanding of your explanation the result should be 4. =SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2)) Result = 4 -- Biff Microsoft Excel MVP "kristap" wrote in message ... Hi Biff, This is what I got from what you said, I must have done something wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#9
|
|||
|
|||
sumproduct help
Thank you so much. That worked... Sorry for all the confusion....
My husband is in bed right now, but I'm almost positive that the only sixes are x4 and x8...... If by chance there are other sizes like x6 or whatever, can I do something like: =SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="")*Count)+((RIGHT(Si ze,2)="x4")*Count/2)) or would I need to put someting in the quotes for it to be accurate? "T. Valko" wrote: We can shorten that a few keystrokes: =SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="x8")*Count)+((RIGHT( Size,2)="x4")*Count/2)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, I think I understand now... For Type xps... Any size x8 = 1 Any size x4 = 1/2 =SUMPRODUCT(--(Type="xps"),((ISNUMBER(SEARCH("x8",Size)))*Count) +((ISNUMBER(SEARCH("x4",Size)))*Count/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message news Hi Biff, Sorry for the confusion. Lets see if I can explain this better.... Each warehouse is set up as its own sheet. And its set up to look like this. ......A...........B.......C....D......E.........F. .....G............................etc count hold wo qc type sku size 1 4 xps 4x4 2 23 iso 1x8 3 56 xps 4x8 4 4 xps 1x8 5 34 xps 4x8 6 23 xps 2x4 They have a quantity report that they have been manually figuring and entering the totals of each type into, and my husbands boss wants him to have it automatically do it for each of their warehouses. The report is set up like this (I will only do it for 1 type instead of all the columns for all the types) ........A................B........C........D...... ..... 2 3 warehouse XPS 4 Good Bad Hold 5 Beltman ? ? ? 6 They want to be able to look at this report and tell what each warehouse has in it. So in the example, I would need how many good xps were at the beltman warehouse. (A1:A6)..... But they want anything that is ?x4(4x4, 1x4 etc) to be divided by 2 because they can fit 2 pallets in the 4x8 space...need to also include in everything that is xps no matter what size......Hope this makes sense. "T. Valko" wrote: Is this what your data looks like: ...........A..........B..........C 1......Type....Size........Qty 2........1........4x4..........2 3........1........4x8..........1 4........1........4x4..........4 5........2........4x8..........5 For Type 1, based on my understanding of your explanation the result should be 4. =SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2)) Result = 4 -- Biff Microsoft Excel MVP "kristap" wrote in message ... Hi Biff, This is what I got from what you said, I must have done something wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
#10
|
|||
|
|||
sumproduct help
If by chance there are other sizes like x6 or whatever,
can I do something like: RIGHT(Size,2)="" No. How should x6 be handled? As the formula is currently written x6 is ignored. Should it be counted as 1? -- Biff Microsoft Excel MVP "kristap" wrote in message ... Thank you so much. That worked... Sorry for all the confusion.... My husband is in bed right now, but I'm almost positive that the only sixes are x4 and x8...... If by chance there are other sizes like x6 or whatever, can I do something like: =SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="")*Count)+((RIGHT(Si ze,2)="x4")*Count/2)) or would I need to put someting in the quotes for it to be accurate? "T. Valko" wrote: We can shorten that a few keystrokes: =SUMPRODUCT(--(Type="xps"),((RIGHT(Size,2)="x8")*Count)+((RIGHT( Size,2)="x4")*Count/2)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, I think I understand now... For Type xps... Any size x8 = 1 Any size x4 = 1/2 =SUMPRODUCT(--(Type="xps"),((ISNUMBER(SEARCH("x8",Size)))*Count) +((ISNUMBER(SEARCH("x4",Size)))*Count/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message news Hi Biff, Sorry for the confusion. Lets see if I can explain this better.... Each warehouse is set up as its own sheet. And its set up to look like this. ......A...........B.......C....D......E.........F. .....G............................etc count hold wo qc type sku size 1 4 xps 4x4 2 23 iso 1x8 3 56 xps 4x8 4 4 xps 1x8 5 34 xps 4x8 6 23 xps 2x4 They have a quantity report that they have been manually figuring and entering the totals of each type into, and my husbands boss wants him to have it automatically do it for each of their warehouses. The report is set up like this (I will only do it for 1 type instead of all the columns for all the types) ........A................B........C........D...... ..... 2 3 warehouse XPS 4 Good Bad Hold 5 Beltman ? ? ? 6 They want to be able to look at this report and tell what each warehouse has in it. So in the example, I would need how many good xps were at the beltman warehouse. (A1:A6)..... But they want anything that is ?x4(4x4, 1x4 etc) to be divided by 2 because they can fit 2 pallets in the 4x8 space...need to also include in everything that is xps no matter what size......Hope this makes sense. "T. Valko" wrote: Is this what your data looks like: ...........A..........B..........C 1......Type....Size........Qty 2........1........4x4..........2 3........1........4x8..........1 4........1........4x4..........4 5........2........4x8..........5 For Type 1, based on my understanding of your explanation the result should be 4. =SUMPRODUCT(--(Type=1),((Size="4x8")*Qty)+((Size="4x4")*Qty/2)) Result = 4 -- Biff Microsoft Excel MVP "kristap" wrote in message ... Hi Biff, This is what I got from what you said, I must have done something wrong because I keep getting an error. I can't use 4x8 because there is a couple of other sizes mixed in as well like 1x8, 2x8 so I need to use all of them... Can I use a wild card like ?x8 or like that? =SUMPRODUCT(--(Beltman!E12:E21=B3),((Beltman!G12:G21="")*A12:A21 )+((Beltman!G12:G21="4x4")*Qty/2)) "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(Type=B3),((Size="4x8")*Qty)+((Size="4z4")*Qty/2)) -- Biff Microsoft Excel MVP "kristap" wrote in message ... First I want to say thank you for all the help everyone is here. Now for my question. Still helping my husband do his spreadsheet for work with all his warehouses. This is what I have: =SUMPRODUCT(--(Beltman!E12:E21=B3),Beltman!A12:A21) where E12:E21 is for the Material Type B3 is material type that I want to get a total number on A12:A21 is how many of that type Now I can't figure out the other half of this. Most of the material type has a size of 4x8 but, some have a size of 4x4. The ones that are 4x4, i need to add and then divide by 2 so it will give me an amount as if it were a 4x8. But I need a calculation that will give me 1 number to represent a total number of all. I hope this makes sense. The calculation as I have it, give me a total number but doesn't cut the 4x4 in half. Basically they can fit 2 of the 4x4 in a 4x8 slot so they want to know how many 4x8's they have... Please help and if its confusing, I can try again to explain it. krista |
|
Thread Tools | |
Display Modes | |
|
|