+ Reply to Thread
Results 1 to 11 of 11

Sumif Criteria from List in another column

  1. #1
    Registered User
    Join Date
    04-14-2018
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    11

    Sumif Criteria from List in another column

    Hi, Not sure if this is possible. But, I was trying to figure out if I could make a criteria from another sheet column

    For Example I have this,
    =SUM(SUMIFS(tAMEX!F:F,tAMEX!C:C,{"*Comcast*","*verizon*"}, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))
    I would like to take {"*Comcast*","*verizon*"} out and put those values in a column on another sheet.

    For example,
    =SUM(SUMIFS(tAMEX!F:F,tAMEX!C:C,Criteria!A:A, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))

    Obviously that doesn't work.

    Is there away to make this work? Or a way to make the first formula cleaner. For instance if I wanted to add a bunch of criterias?.

    Thanks in advance!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Sumif Criteria from List in another column

    Hi Dormie,

    I think you are looking for Advanced Filters, that allow many criteria.

    See:
    http://www.excel-easy.com/examples/advanced-filter.html
    for an example.

    If the criteria is on the same row, it means AND
    If the criteria is on different rows, it means OR
    You can have as many rows of criteria as you want/need. Using stuff like "*Comcast*" is allowed as wildcard searches.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,503

    Re: Sumif Criteria from List in another column

    Or find the section titled "Example 3. SUMPRODUCT & SUMIF" via the link below.
    https://www.ablebits.com/office-addi...iple-criteria/

    BSB

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Sumif Criteria from List in another column

    Removed by JT
    Last edited by JohnTopley; 04-14-2018 at 11:40 AM.

  5. #5
    Registered User
    Join Date
    04-14-2018
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Sumif Criteria from List in another column

    Thank you for the quick responses. I'm taking a look now at them.

  6. #6
    Registered User
    Join Date
    04-14-2018
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Sumif Criteria from List in another column

    Hi, Just wanted to give an update on this.

    BSB - Thank you for link it had exactly what I need.

    In case anyone finds this and is looking for the same solution

    =sumproduct()
    Is the solution.

    =SUM(SUMIFS(tAMEX!F:F,tAMEX!C:C,{"*Comcast*","*verizon*"}, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))
    Revised to:
    =SUMPRODUCT(SUMIFS(tAMEX!F:F,tAMEX!C:C,Criteria!A1:A100, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))
    Then list criteria in Sheet Criteria Column A

    As a side note, A:A crashed my excel (365) a couple of times. So I adjusted to A1:A100 or as needed.

    Certainly a much neater/cleaner way to look at it.

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,503

    Re: Sumif Criteria from List in another column

    Glad I could help

    Personally I'd avoid using entire column references such as F:F when SUMPRODUCT is involved as it's not overly efficient when referring to entire rows/columns.

    My approach is usually to use dynamic named ranges that expand/contract as you add/remove data, that way the formula only ever looks at the rows used.

    BSB

  8. #8
    Registered User
    Join Date
    04-14-2018
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Sumif Criteria from List in another column

    Good tip, I'll have to look into that. How difficult is that? Do you have a quick example?

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,503

    Re: Sumif Criteria from List in another column

    Here you go. Press Ctrl+F3 to open the name manager. You'll see three named ranges, one for each column in the data. The ranges are defined via formulas that for Column A looks like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I've used these three named ranges in the formula (yellow cell) that resembles that your formula.

    Add/remove data that matches the criteria used (green cells) and you'll see that the ranges expand/contract to match.

    Note you don't have to create a named range for every column, just the ones you wish to use in formulas.

    You could also achieve the same with the use of tables.

    BSB
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-14-2018
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Sumif Criteria from List in another column

    BSB, That's great! I've never worked with that ranges like that before.

    Thank you for the sheet, I messed around with it and it's definitely something that would work great for my use.

    I'm sure I'll have a couple of additional questions!

    I'll let you know what I come up with.

    Thank you again!

  11. #11
    Registered User
    Join Date
    04-14-2018
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Sumif Criteria from List in another column

    Just wanted to follow up.

    I've sent up the named range and it works great!

    Thank you for your help.


    I do have an additional question on setting up a sumproduct/search to test each row and true of false. Not sure if I should post it as another topic or not.
    Last edited by Dormie; 04-17-2018 at 02:04 PM.

+ 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] Multiple Criteria SUMIF Using Column as Negative Criteria
    By freybe06 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2014, 04:46 PM
  2. [SOLVED] SUMIF from a list of criteria
    By ncurran217 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-14-2013, 11:10 AM
  3. Sumif with criteria list
    By Brian Barbre in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. Sumif with criteria list
    By KL in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 07:05 AM
  5. Sumif with criteria list
    By KL in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 AM
  6. [SOLVED] Sumif with criteria list
    By KL in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 04:05 AM
  7. Sumif with criteria list
    By Brian Barbre in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. Sumif with criteria list
    By Brian Barbre in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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