+ Reply to Thread
Results 1 to 2 of 2

Sumifs all values not in a list

  1. #1
    Registered User
    Join Date
    06-15-2012
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Sumifs all values not in a list

    I have used the following formula to sum all items within a budget with the same code and period.

    =SUMIFS('Actual 2013'!$F:$F,'Actual 2013'!$A:$A,"="&$A8,'Actual 2013'!$B:$B,"="&$B8)

    Code criteria = A8
    Code range = Actual 2013'!$A:$A
    Dept criteria = B8
    Dept range = Actual 2013'!$B:$B

    The code criteria runs from A8 to A15 say.

    I would like to now pick up all values within the Code range that don't match the codes from A8 - A15, but still matching the B8 department.

    I have thought or using <> for each A8 - A18, individually, but where there are more codes I would run out of argurements so to speak. In addition, I have use OR before, but not sure how to introduce this to this formula.

    thinking, but getting not very far, but hey I am trying.

    =SUMIFS(OR('Actual 2013'!$F:$F,'Actual 2013'!$A:$A,"<>"&$A8,"<>"&$A9, etc....)'Actual 2013'!$B:$B,"="&$B8)

    Is there a sumif, not in list option I could use?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Sumifs all values not in a list

    Try with SUMPRODUCT:
    Please Login or Register  to view this content.
    In which, COUNTIF($A$8:$A$15,A:A) counts 0 ==> 1-COUNTIF(...) counts 1, where A:A unmatch.
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1