Results 1 to 4 of 4

Thread: Nested subtraction in SUMIFS
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Nested subtraction in SUMIFS

    Hi,

    I'm trying to populate a formula that sums all the sales of a particular item with few conditions:

    =SUMIFS(Table1[Sales New] - Table1[Sales Change]; Table1[Sales New] - Table1[Sales Change];">0";Table1[Sales New];">0";Table1[Item];"Blue shirt")

    --> Table1[Sales New]-Table1[Sales Change] = Old Sales of the blue shirt
    --> Table1[Sales New] = New Sales of the blue shirt
    --> Table1[Item];"Blue shirt" = Find item "Blue shirt" from list of items

    So this is what I want: SUM all blue shirts' Old Sales, IF Old Sales AND New Sales are both over 0.

    Excel however says that there's an error with this formula. Any thoughts?

    I know I could get around this by creating a column for Old Sales but for the sake of the file size, I want to keep the number of columns as minimum as possible.

    I also tried this: Table1[Price Old] * Table1[Quantity] to get the old sales but this also caused an error.

    =SUMIFS(Table1[Price Old] * Table1[Quantity]; Table1[Price Old] * Table1[Quantity];">0";Table1[Sales New];">0";Table1[Item];"Blue shirt")

    Thanks in advance!

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Nested subtraction in SUMIFS

    Sumifs only accepts ranges for its sum ranges and criteria ranges. You would have to use sumproduct to do subtraction.

    =SUMPRODUCT((Table1[Sales New] - Table1[Sales Change]),--((Table1[Sales New] - Table1[Sales Change])>0),--(Table1[Sales New]>0),--(Table1[Item]="Blue shirt"))
    Looking for opportunities

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nested subtraction in SUMIFS

    Hi,

    That formula returns #N/A. Is the formula affected if e.g. some sales of the blue shirt is #N/A? Below is an example data:

    Date Shirt Sales New Sales Change (from last year)
    Monday Blue shirt 22 +2
    Monday White shirt 25 -5
    Tuesday Blue shirt #N/A #N/A
    Wednesday Blue shirt 15 +5
    Wednesday Red shirt 20 0

    Because Tuesday's blue shirt data is unavailable, is my SUMPRODUCT formula affected by it and causes the formula to return #N/A?

    If #N/A doesn't affect the SUMPRODUCT, what else might be the problem?

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Nested subtraction in SUMIFS

    Yes you will have to correct the n/a for all entries in the column. Use an iferror formula. You could also do with using the same headers as you suggested in post 1.
    Looking for opportunities

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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