+ Reply to Thread
Results 1 to 5 of 5

SumIfs with multiple criteria (search column for cells w/ text containing)

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    SumIfs with multiple criteria (search column for cells w/ text containing)

    Hi Guys—

    I have been toiling on this all day and cannot seem to marry these two formulas into one.

    Here's the setup:

    Lookup section:
    Column B - Job-specific region
    Column F - Job-specific activity code
    Column H - Job-specific unique identifiers

    Database section:
    Column AZ - long strings of text containing unique identifiers (addresses, project numbers, etc.) [Ex: "13428619 1DKD7801-413 AC 935 W11TH ST"]
    Column BC - regions
    Column BA - activity codes
    Column BE - dollar amounts (from which the final sumifs will tally)

    In column S (let's say cell S72) are the cells where I need the formula to return a vlookup/index-match of B72 in BC, F72 in BA and then search AZ for text containing the text in H72 (935 W11TH ST for this example).

    This formula works correctly:=SUMIFS(BE77:BE81,BC77:BC81,B79,BA77:BA81,F79)

    It's when I introduce the unique identifier text string search. (cell H72: "935 W11TH ST" within "13428619 1DKD7801-413 AC 935 W11TH ST")

    This formula for some reason returns "FALSE" even though the H72 text exists several times in column AZ:=IF(ISNUMBER(SEARCH("H72",AZ77:AZ81)),"")
    it does not work with or without "quotes" around H72

    I just really need the complete formula which searches for all 3 criteria.

    Thanks in advance. I hope someone may be able to help.
    J

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: SumIfs with multiple criteria (search column for cells w/ text containing)

    You are entering that IF(ISNUMBER) as an array formula, right? If not it's only going to test the first entry in AZ77, not all of them through to AZ81.

    SUMIFS doesn't handle operations in the logical structure too well.

    Have you considered a SUMPRODUCT design instead of SUMIFS? That usually works better when you need to do some heavier lifting with the boolean testing.

    Something Like
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: SumIfs with multiple criteria (search column for cells w/ text containing)

    Hi--

    Thanks so much for your quick reply. I still can't seem to get it to work. I've uploaded a pared down version with slight modifications to the cell references (since I simplified the references for my original question).

    Cell H76 is the cell containing the necessary formula (using your example). I also attempted making the IfIsError formula search the column as an array as suggested but perhaps I'm unclear or doing it incorrectly (as is also demonstrated in the attached sheet).

    Ultimately though H76 is the goal.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: SumIfs with multiple criteria (search column for cells w/ text containing)

    Isn't it:
    =SUMIFS(BE88:BE93, BC88:BC93,B76,BA88:BA93,F76,AZ88:AZ93,"*"&H73&"*")

  5. #5
    Registered User
    Join Date
    08-10-2012
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: SumIfs with multiple criteria (search column for cells w/ text containing)

    Indeed it is! You, my friend, are a life saver! Thank you!

+ 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] SUMIFS Multiple Criteria Same Column
    By jsharrard in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-18-2017, 05:08 PM
  2. sumifs with multiple criteria in one column.
    By apolloni in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-10-2014, 05:13 AM
  3. [SOLVED] SumIf / SumIfs with multiple Contains Text criteria
    By theweirdone in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2012, 05:22 AM
  4. Replies: 4
    Last Post: 08-08-2006, 01:15 PM
  5. Replies: 0
    Last Post: 08-08-2006, 10:35 AM

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