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 |
#11
|
|||
|
|||
sumproduct help
OK I was just searching through a bunch of the other warehouses and there ARE
a few, not many but a few that are NOT x8 or x4..... This is what I have come up with and it worked so far for the 1 that i tried, will it work for them all? The problem I have is the numbers are constantly changing, depending on if they sell material in the warehouse or acquire more, so if they add something that isn't x8 or x4 the numbers won't add up..... Will this work for all or did I just get lucky? =SUMPRODUCT(--(Beltman!E12:E21=B3),((RIGHT(Beltman!G12:G21,2)" x4")*Beltman!A12:A21)+((RIGHT(Beltman!G12:G21,2)=" x4")*Beltman!A12:A21/2)) "kristap" wrote: 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 |
#12
|
|||
|
|||
sumproduct help
I just posted but I can't edit so I will answer this.... I am going to assume
that anything that isn't x4 will be as 1 because he didn't tell me otherwise. And if its x6 or whatever all the others were, then there would be only 1 in the 4x8 slot because I don't think we can double up, because it wouldn't fit. I will clarify with him tomorrow though and may end up posting more questions at that point... He's sleeping (its almost 1:00 AM here) so I will figure the rest out tomorrow... Thank you so much for all your help. You are awesome.... "T. Valko" wrote: 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 |
#13
|
|||
|
|||
sumproduct help
=SUMPRODUCT(--(Beltman!E12:E21=B3),((RIGHT(Beltman!G12:G21,2)" x4")*Beltman!A12:A21)+((RIGHT(Beltman!G12:G21,2)=" x4")*Beltman!A12:A21/2))
That'll probably work. What that means is: Anything** that is not x4 = 1 x4 = 1/2 ** anything means literally *anything* including empty cells -- Biff Microsoft Excel MVP "kristap" wrote in message ... OK I was just searching through a bunch of the other warehouses and there ARE a few, not many but a few that are NOT x8 or x4..... This is what I have come up with and it worked so far for the 1 that i tried, will it work for them all? The problem I have is the numbers are constantly changing, depending on if they sell material in the warehouse or acquire more, so if they add something that isn't x8 or x4 the numbers won't add up..... Will this work for all or did I just get lucky? =SUMPRODUCT(--(Beltman!E12:E21=B3),((RIGHT(Beltman!G12:G21,2)" x4")*Beltman!A12:A21)+((RIGHT(Beltman!G12:G21,2)=" x4")*Beltman!A12:A21/2)) "kristap" wrote: 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 | |
|
|