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

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)}

Thank you very, very much! It worked

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!

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.

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

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

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

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.

Try

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

Enter with Ctrl+Shift+Enter