+ Reply to Thread
Results 1 to 8 of 8

Sumifs without a matching criteria

  1. #1
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Lightbulb Sumifs without a matching criteria

    Hello All, I am wondering if there's a formula I could use like a sumifs that would add all the data even if a particular criteria is not found in the range.

    Example:

    =SUMIFS($O$1:$O$16,$N$1:$N$16,"Yes",$M$1:$M$16,"Yes",$L$1:$L$16,"Yes")

    The above would sum all the numbers if columns N,M,L contain "Yes" in them, but what if column M doesn't contain "Yes" I would like the sum of columns N and L still. Is there a formula to make this happen? any help would be appreciated.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Sumifs without a matching criteria

    If I understand correctly try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,554

    Re: Sumifs without a matching criteria

    Maybe?

    =SUMPRODUCT($O$1:$O$16*(($N$1:$N$16="Yes")+($M$1:$M$16="Yes")+($L$1:$L$16="Yes")>0))

  4. #4
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Sumifs without a matching criteria

    Phuocam

    It's almost working but the answer I should get is 9 not 21. I understand what the formula is doing to get 21, but I need for the answer to be 9.
    Attached Files Attached Files
    Last edited by lsantana; 10-27-2020 at 10:08 PM.

  5. #5
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Sumifs without a matching criteria

    FlameRetired

    This would work, but is not dynamic enough because my original data is about 9 columns and I would have to do about 36 combinations with this method.

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,554

    Re: Sumifs without a matching criteria

    Try this:

    =SUMPRODUCT($O$2:$O$17*(($L$2:$L$17=E2)+($M$2:$M$17=E2)+(N2:$N$17=E2)>1))

    If you have multiple combinations, you can use the helper column.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,488

    Re: Sumifs without a matching criteria

    Here's another one to try:

    =SUMIFS($O$1:$O$16,$N$1:$N$16,if(countif($N$1:$N$16,"Yes"),"Yes","*"),$M$1:$M$16,if(countif($M$1:$M$16,"Yes"),"Yes","*"),$L$1:$L$16,if(countif($L$1:$L$16,"Yes"),"Yes","*"))

    Hope this helps.

    Pete
    Last edited by Pete_UK; 10-28-2020 at 05:56 AM. Reason: missing comma

  8. #8
    Registered User
    Join Date
    05-16-2017
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    51

    Re: Sumifs without a matching criteria

    Quote Originally Posted by Phuocam View Post
    Try this:

    =SUMPRODUCT($O$2:$O$17*(($L$2:$L$17=E2)+($M$2:$M$17=E2)+(N2:$N$17=E2)>1))

    If you have multiple combinations, you can use the helper column.
    ok this work perfect!! thank you so much

+ 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] Sum Sumifs or sumproduct using multiple criteria with matching months
    By billrogers184 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2019, 10:01 AM
  2. Date Not Matching SUMIFS
    By Kozbot in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-02-2019, 09:17 PM
  3. SUMIFS with matching Month
    By vidus in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-08-2017, 11:45 PM
  4. Replies: 0
    Last Post: 08-22-2017, 03:49 PM
  5. [SOLVED] SUMIFS formula that takes first seven of matching criteria from an array
    By Samuel_j in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-24-2015, 05:08 AM
  6. [SOLVED] sumproduct Or sumifs on multiple criteria matching day & time
    By tabkaz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-16-2015, 05:45 AM
  7. Matching, IF, SUMIFS, ISSUE- Help
    By exclusiveicon in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-16-2014, 02:41 PM

Tags for this Thread

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