+ Reply to Thread
Results 1 to 6 of 6

'Sumif' Formula with multiple criteria in one cell

  1. #1
    Registered User
    Join Date
    02-14-2010
    Location
    Scotland
    MS-Off Ver
    MS Office 2013
    Posts
    35

    'Sumif' Formula with multiple criteria in one cell

    Hi All,

    Looking for a little assistance with the below, it has me a little baffled.

    I'm looking to have a 'sumifs' statement that can use a multiple criteria statement (i.e. {1,2,3,4}) and have this directly referenced in a cell.

    Here is a simplified version of the working formula:-

    =SUMIFS(B:B,A:A,{1,2,3,4})

    Where the criteria {1,2,3,4} in the formula is replaced with a cell reference with those contents in. So this may look like:

    =SUMIFS(B:B,A:A,A1) Cell 'A1' Contents = {1,2,3,4}

    I have had multiple attempts to get this to work but have failed and would very much appreciate it if one of you chaps (or ladies) may be able to assist?

    Thanks in advance.

    Stuart

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: 'Sumif' Formula with multiple criteria in one cell

    I can't find a way to put the criteria in a single cell so you may be stuck with

    =SUM(SUMIFS($B:$B,$A:$A,{1,2,3,4}))

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 'Sumif' Formula with multiple criteria in one cell

    I also can't see any realistic way to put all the criteria in a single cell (at least not with sumifs).
    But if you could put the criteria in a Range of cells, say C1:C4
    Then you can do

    =SUMPRODUCT(SUMIF(A:A,C1:C4,B:B))

    C1 = 1, C2 = 2 etc..
    Last edited by Jonmo1; 10-04-2016 at 09:14 AM.

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

    Re: 'Sumif' Formula with multiple criteria in one cell

    Enter the array as a comma space delimited string.

    Data Range
    A
    B
    C
    D
    E
    1
    ------
    ------
    ------
    ------
    ------
    2
    5
    26
    1, 2, 3, 4
    220
    3
    4
    64
    4
    6
    86
    5
    2
    18
    6
    1
    54
    7
    2
    10
    8
    7
    69
    9
    3
    12
    10
    4
    62
    11


    This formula entered in E2:

    =SUMPRODUCT(SUMIF(A:A,--TRIM(MID(SUBSTITUTE(D2,", ",REPT(" ",99)),
    (99*(ROW(INDIRECT("1:"&LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1))-1))+1,99)),B:B))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    02-14-2010
    Location
    Scotland
    MS-Off Ver
    MS Office 2013
    Posts
    35

    Re: 'Sumif' Formula with multiple criteria in one cell

    Thanks AMJonmo1, AMJohnTopley,

    I was afraid you would say that. Thanks for the input, I think I will have to resort to adding the criteria in to multiple columns.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: 'Sumif' Formula with multiple criteria in one cell

    If you put your criteria in 1 cell, with only a comma as a delimiter between each criteria
    Try

    =SUMPRODUCT(--(ISNUMBER(SEARCH(","&$A$1:$A$10&",",","&$D$1&","))),$B$1:$B$10)

    D1 = 1,2,3,4

+ 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. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  2. Sumif formula using multiple criteria
    By juksie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-23-2012, 05:44 AM
  3. sumif formula multiple criteria
    By simpson in forum Excel General
    Replies: 13
    Last Post: 07-04-2011, 07:36 AM
  4. Problem with a SUMIF formula with multiple criteria
    By jat82nd in forum Excel General
    Replies: 6
    Last Post: 04-18-2008, 06:49 PM
  5. Array Formula w/ Multiple SumIf Criteria
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 03:05 PM
  6. [SOLVED] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. [SOLVED] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. Multiple Criteria in a SUMIF formula
    By JCARROLL in forum Excel General
    Replies: 1
    Last Post: 07-20-2005, 05:05 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