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

1. ## 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. ## 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. ## 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!

4. ## 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. ## Re: SumIfs with multiple criteria (search column for cells w/ text containing)

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

##### Users Browsing this Thread

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

#### 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