# AverageIf function with multiple criteria (searching for text strings)using OR logic

1. ## AverageIf function with multiple criteria (searching for text strings)using OR logic

Dear all,

I'm sorry if this is any way wrong or incorrect or if I am unknowingly breaking rules, as this is my first post.
However, I really need help with a problem I have been stuck on for over a week and I simply cannot find a solution.
The issue is really complicated and no matter how many different ways of doing each individual task element of it, I simply am not able to compile it into a working formula.

It's quite a long explanation of the problem, so please do bear with me.

First off, this problem is based on two data tables.
1) The first is a 6 column by 300 row data table of which the 4th and 6th columns will be required in this formula.
2) The second is an 11 column by 40,000 row data table, in which we will be concerned with all columns.

The two tables are on separate pages (when I finish the whole project they will all be integrated into one page), with the former on Sheet1 and the latter on Sheet2

The key is to find the average on an array of values based on criteria. The criteria is checking whether the the 4th column in the former table contains one of ten text strings. It need not have all ten text strings, simply must have one or more of the ten text strings within it.

The ten text strings are individually calculated in the first ten columns of the latter table, and this formula is entered on the 11th column. So for example, A1:J1 will contain individual text strings, e.g "La" or something similar.

What we are trying to do is find out which of the rows in the fourth column of the former table has one or more of the ten text strings within, and then to create an average of all the figures in the corresponding rows of the sixth column.

So if La can be found in the fifth, tenth and eleventh rows, I want to find that out and then average out the values in the sixth column of the former data table at the fifth, tenth and eleventh rows.

Currently my formula looks a bit like this, but it's not working, it always returns a #DIV/0 error.
{=AVERAGE(IF(ISNUMBER(MATCH(Sheet1!\$D\$1:\$D\$300,{"*"&\$A1&"*","*"&\$B1&"*","*"&\$C1&"*","*"&\$D1&"*","*"&\$E1&"*","*"&\$F1&"*","*"&\$G1&"*","*"&\$H1&"*","*"&\$I1&"*","*"&\$J1&"*",},0))="TRUE",Sheet1!\$E\$1:\$E\$300))}

Thank you in advance for all and any help!

Martins1

2. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Excel shouldn't even accept that formula, ranges inside array constants don't compile.

See if either of these gives you the expected results,

{=SUM(IFERROR(AVERAGEIF(Sheet1!\$D\$1:\$D\$300,"*"&\$A1:\$J1&"*",Sheet1!\$E\$1:\$E\$300),0))}

or

{=IFERROR(SUMIF(Sheet1!\$D\$1:\$D\$300,"*"&\$A1:\$J1&"*",Sheet1!\$E\$1:\$E\$300)/COUNTIF(Sheet1!\$D\$1:\$D\$300,"*"&\$A1:\$J1&"*"),0)}

3. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Thank you very, very much! It worked

4. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Quick second question:
I would like to do a countif statement with both AND and OR logic:
Basically, there are 12 criteria:
If the criteria range meets:
Criteria 1
Criteria 2
1 or more of Criteria 3-12
Then i would like to count it. How do i do this?

Thank you!

5. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Something like

=SUMPRODUCT((Range1=Crit1)*(Range2=Crit2)*((((Range3=Crit3)+(Range4=Crit4)+(Range5=Crit5))>0))

Basically, you multiply for AND, you add for OR.

The >0 test at the end of the OR section is to prevent rows being counted multiple times when more than one of the OR criteria is met.

If the results come out wrong, check the parenthesis, if any are missing or misplaced it can mess things up.

6. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Thanks, and if the criteria were the same but I wanted to do a sumifs version of that?

7. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Same principle, just tag the sum range onto the end

=SUMPRODUCT((Range1=Crit1)*(Range2=Crit2)*(((Range3=Crit3)+(Range4=Crit4)+(Range5=Crit5))>0)*SumRange)

Also, noting my own advice on misplaced parenthesis, I had too many opening parenthesis in the last example it should have been

=SUMPRODUCT((Range1=Crit1)*(Range2=Crit2)*(((Range3=Crit3)+(Range4=Crit4)+(Range5=Crit5))>0))

8. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Thank you very much!
In terms of criteria, how would you search if the criteria range(B3:B10) contains a certain substring anywhere within it, in which that certain substring is determined by what is in another cell (L2).
Currently I'm using, for example (B3:B10="*"&L2&"*")
Thanks

9. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

For that you would need to use a couple of extra functions, unlike sumif or countif, sumproduct doesn't accept wildcards.

ISNUMBER(SEARCH(L2,B3:B10))

Depending on requirements, you could use SEARCH or FIND, the only difference being, FIND is case sensitive, where SEARCH is not.

One thing to note, this method is only reliable when you require a * wildcard at both ends of the criteria, searches with a single * wildcard, or any ? single character wildcards can return false positives.

10. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Try

=IF(SUM(--(ISNUMBER(SEARCH(L2,\$B\$3:\$B\$10,1)))),"Y","N")

Enter with Ctrl+Shift+Enter

11. ## Re: AverageIf function with multiple criteria (searching for text strings)using OR logic

Originally Posted by Martins1
Currently I'm using, for example (B3:B10="*"&L2&"*")
If that's all the formula is doing you can use COUNTIF:

=COUNTIF(B3:B10,"*"&L2&"*")

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