+ Reply to Thread
Results 1 to 4 of 4

SUMIFS/COUNTIFS array function question (multiple criteria)

  1. #1
    Registered User
    Join Date
    02-28-2014
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    35

    SUMIFS/COUNTIFS array function question (multiple criteria)

    Hi I have a simple question:

    I know how to use SUMIFS as an array but don't know how to make it work when I do not want to include certain values in the sum:

    EXAMPLE: How can I convert =SUMIFS($A:$A,$N:$N,{"*Criteria 1*","*Criteria 2*","*Criteria 3*"}) into something like this: SUMIFS($A:$A,$N:$N,{"<>"&"*Criteria 1*","<>"&"*Criteria 2*","<>"&"*Criteria 3*"}). The second formula does not work! How can I make it work for text?

    Also I want to sum something not equal to a cell that contains specific text or numbers:
    Another example is this: How to convert the statement below into a SUMIFS of A:A where N:N does NOT contain 1,2,3,4,5 etc...
    =SUMIFS($A:$A,$N:$N,{1,2,3,4,5,6}) - ????

    Thanks for your help

    AK

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: SUMIFS/COUNTIFS array function question (multiple criteria)

    Why is it necessary to express the group of criterion as an array?

    This works just as effectively.

    =SUMIFS(A:A,N:N,"<>"&1,N:N,"<>"&2,N:N,"<>"&3,N:N,"<>"&4,N:N,"<>"&5,N:N,"<>"&6)

    If you do require the use of a nested array, you may wish to use SUMPRODUCT instead.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIFS/COUNTIFS array function question (multiple criteria)

    For the positive result, e.g. sum column A when N = any of 1,2,3,4,5,6 then you need to wrap a SUM function around the SUMIFS, i.e.

    =SUM(SUMIFS($A:$A,$N:$N,{1,2,3,4,5,6}))

    For excluding those values you could just subtract that amount from the SUM of column A, like this:

    =SUM($A:$A)-SUM(SUMIFS($A:$A,$N:$N,{1,2,3,4,5,6}))

    or you can explicitly exclude them as separate conditions like this:

    =SUMIFS($A:$A,$N:$N,"<>1",$N:$N,"<>2",$N:$N,"<>3",$N:$N,"<>4",$N:$N,"<>5",$N:$N,"<>6")

    Both of those approaches can be used with text values too
    Audere est facere

  4. #4
    Registered User
    Join Date
    02-28-2014
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: SUMIFS/COUNTIFS array function question (multiple criteria)

    Thank you so much!

    I totally had forgotten about wrapping everything with the SUM function! details, details...

    Have a good one!

+ 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. Countifs multiple criteria array
    By lamdl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2014, 09:18 PM
  2. [SOLVED] COUNTIFS using multiple criteria - maybe an OR function?
    By Calder in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2014, 12:10 PM
  3. [SOLVED] SUMIFS with multiple criteria evaluated against array?
    By opheim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2013, 09:03 AM
  4. SUMIFS w/ multiple criteria and an or function
    By jacobkmc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-12-2013, 09:22 PM
  5. [SOLVED] Countifs function with multiple criteria in same column
    By sam99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2012, 09:10 AM

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