+ Reply to Thread
Results 1 to 6 of 6

SumIfs() List of Criteria in a column

  1. 02-16-2016, 06:22 PM #1
    McSnoop
    McSnoop is offline
    Registered User
    Join Date
    02-16-2016
    Location
    Nevada, US
    MS-Off Ver
    Professional 2010
    Posts
    3

    SumIfs() List of Criteria in a column

    Hello,

    First just wanted to apologize if below formatting is bad. I'm new here and not quite sure how to format tables yet!

    Anyways!

    I am currently trying to use a formula which checks for a list of names, and if the name is in the list, then to sum that value. For example on a separate sheet I have a list similarly as below.

    x1 x2 x3
    1A 5A 62B
    2A 6A 97A
    3A 13B 99A
    4A 14B 98A
    9B 21C 107B
    10B 22C 106B
    11B 26A 113C
    12B 25A 115C
    17C 33B 114C
    18C 34B 102A
    19C 41C 103A
    20C 42C 110B
    7A 30A 111B
    8A 38B 118C
    15B 40B 130B

    On another sheet I have a ton of different combinations of these numbers and letters. Next to these numbers and letters is a value. I need to sum all the values that match my list in x1.

    Currently I am using a formula such as this.

    =SUM(SUMIF(A157:A329,{"1A";"2A";"3A";"4A";"9B";"10B";"11B";"12B";"17C";"18C";"19C";"20C";"7A";"8A";"15B"},B157:B329))

    The issue is this requires me to go into each different equation I have and manually add the additional number/value every time I add something to the above x1,x2,x3 lists. Is there a way to dynamically do these sums so that it will check the column, and if a number/letter is added to the column it will also add that one?

    Thanks for any help!
    Reply With QuoteRegister To Reply
  2. 02-16-2016, 07:13 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: SumIfs() List of Criteria in a column

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 02-16-2016, 07:30 PM #3
    McSnoop
    McSnoop is offline
    Registered User
    Join Date
    02-16-2016
    Location
    Nevada, US
    MS-Off Ver
    Professional 2010
    Posts
    3

    Re: SumIfs() List of Criteria in a column

    Thanks for the help! I believe my example file should now be attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-16-2016, 07:51 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SumIfs() List of Criteria in a column

    I didn't download your file.

    =SUM(SUMIF(A157:A329,{"1A";"2A";"3A";"4A";"9B";"10B";
    "11B";"12B";"17C";"18C";"19C";"20C";"7A";"8A";"15B"},B157:B329))
    Try it like this...

    Assume the X1 list range is A1:A15

    =SUMPRODUCT(SUMIF(A157:A329,A1:A15,B157:B329))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 02-16-2016, 08:03 PM #5
    McSnoop
    McSnoop is offline
    Registered User
    Join Date
    02-16-2016
    Location
    Nevada, US
    MS-Off Ver
    Professional 2010
    Posts
    3

    Re: SumIfs() List of Criteria in a column

    This worked perfectly! I wasn't aware of the sumproduct formula. Thanks for your help!
    Reply With QuoteRegister To Reply
  6. 02-16-2016, 08:06 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SumIfs() List of Criteria in a column

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIFS to reference cell containing list of names for criteria
    By Gareth1000 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-01-2016, 02:36 PM
  2. Sumifs with one criteria being a list
    By lorber123 in forum Excel General
    Replies: 6
    Last Post: 09-22-2014, 11:11 AM
  3. [SOLVED] SumIFs, using a list as possible criteria...
    By DeeRok in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-30-2014, 07:30 PM
  4. [SOLVED] sumifs, multiple criteria, one of them being a list?
    By PowerZ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-16-2014, 06:23 PM
  5. Using a Named List as a criteria in Sumifs Formula
    By yoshik in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-15-2013, 02:14 PM
  6. Help using a list/array as criteria in SUM(SUMIFS())
    By jaredmason in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2012, 08:20 PM
  7. [SOLVED] SUMIFS - multiple criteria on one list?
    By tangcla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2012, 09:52 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1