+ Reply to Thread
Results 1 to 11 of 11

Include and exclude items using SUMIFS and wildcards

  1. #1
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Question Include and exclude items using SUMIFS and wildcards

    Hi.
    There are two questions inherent to this thread. But let's go step-wise:

    I have this SUMIFS formula in which I want to include items (Description) that contains ANY of the words in cell C16 and C17:
    = SUMIFS(Value; Description; "*"& C16 &"*"; Description; "*"& C17 &"*")


    Unfortunately, only the items that contain BOTH words are being included in the sum.
    Any idea on how to get around this?
    Attached Files Attached Files
    Luis A. D. Ah-Hoy

  2. #2
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Include and exclude items using SUMIFS and wildcards

    Thanks for the quick reply, daddylonglegs.
    The only problem with your formula is that the items that include BOTH words are being doubly added to the sum.
    Thus, the result is 6.750 instead of 3.650.

    It will be brilliant if the "double add" is eliminated.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Include and exclude items using SUMIFS and wildcards

    You can use this version

    =SUMPRODUCT(SUMIFS(Value;Description;"*"& C16:C17 &"*"))

    ...but that will double count any rows that contain both....so if you don't want that you can subtract the result of the first formula which only counts both
    Audere est facere

  4. #4
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Include and exclude items using SUMIFS and wildcards

    That certainly works.

    But the criteria fields will grow much larger and resorting to the subtraction of the two formulas will prove very fatiguing, resulting in a relatively intricate and long formula.

    Therefore, a more compact method is required.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Include and exclude items using SUMIFS and wildcards

    The following array entered formula* yields the expect 3650 for the sample data given:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell containing the formula is in edit mode.
    Edit: The array entered formula may also be written as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Last edited by JeteMc; 03-15-2017 at 11:24 PM. Reason: Added Edit
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Include and exclude items using SUMIFS and wildcards

    Worked wonderfully, JeteMc.
    Thanks!


    Would it be possible now to accommodate the second set of criteria?
    This means that based on the previous calculations, we're now only counting:
    • The items that correspond to 11401* ("Analytical Center" begins with...), and;
    • The items that correspond to 6511* ("Depreciation Account" begins with...)?

    This will of course, require the use of wildcards which are kinda tricky.
    Tried to tweak your formula myself, but without success.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Include and exclude items using SUMIFS and wildcards

    Try the following array entered formula* (see post #5 for activation):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    03-14-2017
    Location
    Maputo, Mozambique
    MS-Off Ver
    2013
    Posts
    15

    Re: Include and exclude items using SUMIFS and wildcards

    That will do, Jete.
    And I won't even bother you with the inclusion of other criteria. It has become quite clear that the formula will become so complex, that I'd rather use a combination of formulas to get the final result.

    As for your suggestions, I certainly learned a lot from it.
    Big thanks for that!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Include and exclude items using SUMIFS and wildcards

    You're Welcome and thank you for the feedback. I think that you are wise to break the final result into multiple formulas. Smaller formulas are easier to trouble shoot and according to "some sources" run faster. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Include and exclude items using SUMIFS and wildcards

    Quote Originally Posted by JeteMc View Post
    =SUMPRODUCT(IF(IF(IFERROR(SEARCH(C16,B6:B11),FALSE),1,0)+(IF(IFERROR(SEARCH(C17,B6:B11),FALSE),1,0))>0,1,0),E6:E11)
    Hi JeteMc,

    If you do this slightly differently (without IFs) then you can use a version which doesn't need to be "array entered", i.e.

    =SUMPRODUCT((ISNUMBER(SEARCH(C16,B6:B11))+ISNUMBER(SEARCH(C17,B6:B11))>0)+0,E6:E11)

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Include and exclude items using SUMIFS and wildcards

    @daddylonglegs, VERY NICE!
    By extension a regular formula that incorporates the second set of inclusions could be written:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. sumifs include/ include criteria
    By Burt_100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2016, 07:34 AM
  2. [SOLVED] Sumifs with wildcards
    By rs1aj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2016, 05:47 PM
  3. [SOLVED] Include/Exclude certain sheets in loop
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-08-2015, 06:14 AM
  4. Set filter to EXCLUDE values rather than include them
    By Uther_d_dragon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2013, 10:54 AM
  5. [SOLVED] Re: Comparing Data Need to Include & Exclude
    By in forum Excel General
    Replies: 1
    Last Post: 06-06-2006, 10:30 AM
  6. [SOLVED] Comparing Data Need to Include & Exclude
    By in forum Excel General
    Replies: 1
    Last Post: 06-06-2006, 09:10 AM
  7. Comparing Data Need to Include & Exclude
    By in forum Excel General
    Replies: 1
    Last Post: 06-05-2006, 03:40 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