+ Reply to Thread
Results 1 to 2 of 2

SUMIFS, exclude range of criteria

  1. #1
    Forum Contributor ABSTRAKTUS's Avatar
    Join Date
    04-18-2010
    Location
    England
    MS-Off Ver
    Win10 Excel 2016
    Posts
    609

    SUMIFS, exclude range of criteria

    Hi XL Maestros,

    Here I have a SUMIFS where I would like to exclude a dynamic range of values in a column (number of values are changing constantly). I cannot find a similar solution on web, I wonder if anybody has got a workaround or something? The syntax I'm after is something like this:

    Please Login or Register  to view this content.
    Any help will be much appreciated!

    Edited - I suppose the solution, if there will be one, will apply to COUNTIFS too?

    Thanks
    Last edited by ABSTRAKTUS; 02-06-2020 at 07:45 AM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,727

    Re: SUMIFS, exclude range of criteria

    How about
    =SUMIFS($B$2:$B$100,$C$2:$C$100,$C$1,$D$2:$D$100,"<>"&$E$2:$E$20)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Is it possible to exclude certain cells from DGET Criteria Range?
    By ajeshkdy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2019, 08:06 AM
  2. Replies: 3
    Last Post: 02-02-2017, 04:32 AM
  3. Replies: 5
    Last Post: 10-03-2016, 03:39 AM
  4. [SOLVED] COUNTIFS forumulas that can exclude criteria in a range
    By jholiday78 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-05-2015, 12:00 PM
  5. [SOLVED] =sum(countifs(range,{not this, not this, nor this} -How to exclude criteria in this manner
    By nscarritt in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-26-2014, 04:00 PM
  6. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

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