Hi All,
This is my first time here posting. I have a problem getting a Sumif to function they way i want it to.
Capture.JPG
=SUMIF($A$2:A2,A2,$B$2:B2)
I've attached a test sheet so you can see what i mean as im very poor at explaining.
This seems to do what i want but skips the top result, im thinking its because it only has a single number and therefore nothing to do a sum on however it still spits out the result of 1 but doesnt add this single 1 to the next result...
I have a stock report and a bill of material. I want to bounce my stock report off my bill of material and have the required serial numbers sum up to show me a total overall requirement at each instance, however i dont want to show me the entire result on each repeated line, i want it to tally up as it goes so that i can show available stock decrease as it goes along until it shows me negative numbers.
E.g. Serial number 1234 shows up 3 times in the list of 1200 items, each item of 1234 requires me to pick 2pcs each time, i want the sumif to show me 2pcs on the first instance, then 4 on the next and 6 on the last. If i have 5pcs in stock i will be able to show that on the third instance i will have a -1 result and know i have a shortage.
I have over 1200 serial numbers per bill of material and i only want to know what im short of and at what part of the bom i will see the shortage.
I'm sure this is stupid easy but i just cant work out how to get this to function as needed...
Thank you for your help
Bookmarks