+ Reply to Thread
Results 1 to 6 of 6

Sumifs - two criteria where one criteria is to match anything in a range

  1. #1
    Registered User
    Join Date
    12-15-2016
    Location
    Stockholm
    MS-Off Ver
    Excel for Mac 15.28
    Posts
    3

    Sumifs - two criteria where one criteria is to match anything in a range

    I want to sum a column, where BOTH another column = a value in one cell, AND yet another column equals anything from a defined range.

    EG:

    A B C D E
    Customer Accounting code Value
    1 ABC 3000 $400
    2 ABC 4037 $35
    3 DEF 3000 $5674
    4 ABC 3050 $500
    5
    6 ABC
    7
    8 3000
    9 3050
    10 3100
    11

    I want something like =sumifs(C1:C4;A1:A4;"="&A6;B1:B4;"="&{A8:A10})
    The answer to this example should be $900


    But it doesn't work with a range as the criteria.

    I've seen
    =SUM(SUMIFS(C1:C4;B1:B4;{"3000","3050"}))
    and
    =SUMPRODUCT(--ISNUMBER(SEARCH(A1:A4;A6)))>0
    and
    =SUM(IF(ISERROR(MATCH(B1:B4;A8:A10;0))=FALSE;C1:C4;0))

    But none of these allow for the (changeable) range as one criteria AND a second criteria.

    Any suggestions?
    Thanks
    Last edited by FHen; 12-15-2016 at 03:47 PM.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs - two criteria where one criteria is to match anything in a range

    Maybe this...

    =SUMPRODUCT(SUMIFS(C1:C4;A1:A4;A6;B1:B4;A8:A10))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    12-15-2016
    Location
    Stockholm
    MS-Off Ver
    Excel for Mac 15.28
    Posts
    3

    Re: Sumifs - two criteria where one criteria is to match anything in a range

    Nope. Still getting zero. And it doesn't work with =SUMPRODUCT(SUMIFS(C1:C4;B1:B4;A8:A10)) either.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs - two criteria where one criteria is to match anything in a range

    Works for me.

    Here's a sample file...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-15-2016
    Location
    Stockholm
    MS-Off Ver
    Excel for Mac 15.28
    Posts
    3

    Re: Sumifs - two criteria where one criteria is to match anything in a range

    Great. Thanks!

    Do you know if there's any way to make this work in Google Sheets?

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs - two criteria where one criteria is to match anything in a range

    Quote Originally Posted by FHen View Post
    Do you know if there's any way to make this work in Google Sheets?
    Sorry, I have no experience with Google spreadsheets.

+ 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. Replies: 5
    Last Post: 10-03-2016, 03:39 AM
  2. How to sum SUMIFS - multiple criteria for one criteria range???
    By trstew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-06-2016, 02:37 PM
  3. [SOLVED] SUMIFS to match array of Criteria
    By amazinglazers in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-19-2013, 06:25 AM
  4. SUMIFS with Multiple Criteria and a Partial match
    By paulbeav in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-28-2013, 04:00 PM
  5. SUMIFS with a criteria range ?
    By vemix in forum Excel General
    Replies: 6
    Last Post: 12-20-2012, 11:56 PM
  6. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 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