I have a list of various scrap materials inventory by weight (kg) that is reported out weekly. Here's an example:
MATERIAL CODE MATERIAL TYPE 2-Jan-17 9-Jan-17 16-Jan-17 23-Jan-17 30-Jan-17 Feb-06-17 Feb-13-17 Feb-21-17 Feb-27-17 55213 ABS 1826 1826 2795 2896 2,896 0 2,104 2,512 3,526 59381 ABS 8949 8949 8111 3984 6,294 6,619 7,472 6,668 5,807
I want to see the total weight of scrap, by material, that was added to inventory. Is there a way to use SUMIF/SUMIFS function to show total positive changes or something else? Is there a way to do it without adding a second column to capture the cumulative changes?
Any help would be greatly appreciated!
Difficult without a helper column.
If values are in cells C2:K2, total positive changes can be summed with (cell M2 copied down):
=SUMPRODUCT((D2:K2-C2:J2)*((D2:K2-C2:J2)>0))
You say totals by material, but you have materialcode and material type, so here's both:
Mat Code (cell N2 copied down):
=SUMIF($A$2:$A$3,$A2,$M$2:$M$3)
and Mat type: (cell O2 copied down):
=SUMIF($B$2:$B$3,$B2,$M$2:$M$3)
There's an image above, click it to get full size if you can't see it.
Bookmarks