Results 1 to 2 of 2

Thread: SUM of Total Positive Changes in a Column

  1. #1
    Neophyte MRADICE's Avatar
    Join Date
    Feb 2019
    Posts
    1
    Articles
    0
    Excel Version
    Excel 2013

    SUM of Total Positive Changes in a Column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,597
    Articles
    0
    Excel Version
    365
    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)
    Click image for larger version. 

Name:	2019-02-14_185103.jpg 
Views:	8 
Size:	53.7 KB 
ID:	8892
    There's an image above, click it to get full size if you can't see it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •