AVERAGEIFS() problem with partial text

[StefanoM]

Dear All,
I'm new to the forum and this is my first thread!
I have the below spreadsheet that I want to be dynamic in choosing data to make the correct average.
In the coloumns from C to K there are the data.
In the coloumns from M to Q there are the formulas I'm trying to sort out.
I want to average the values in the table based on the letter I put in green
For example the formula in M10 is =(AVERAGEIFS($C10:$K10;$C$4:$K$4;$M$4)) and the result is 85, the avarage of all the "A" letters in the table for the corresponding row.
Of course the formula looks for the exact match of the cell choosen, but it doesn't work if I put a combination of the cells I want to average.
In fact the columns N and O don't work and I wasn't able to use wildcards to make it function properly.
I want to put "AB" in the cell N4 and have, for example in cell N10, 183 as a result of the average of all the A and B letters in the table.
Thank you for your help!

[TABLE="width: 1238"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mean Recovery (%)
[/TD]
[TD]Mean Recovery (%)
[/TD]
[TD]Mean Recovery (%)
[/TD]
[TD]Mean Recovery (%)
[/TD]
[TD]Mean Recovery (%)
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Level
[/TD]
[TD]A
[/TD]
[TD]A
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]B
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]C
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD]A
[/TD]
[TD]AB
[/TD]
[TD]ABC
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Replicate
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Condition
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Lim Min
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Lim Max
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Analyte
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Li7
[/TD]
[TD]85
[/TD]
[TD]87
[/TD]
[TD]82
[/TD]
[TD]284
[/TD]
[TD]288
[/TD]
[TD]273
[/TD]
[TD]423
[/TD]
[TD]435
[/TD]
[TD]409
[/TD]
[TD][/TD]
[TD]85
[/TD]
[TD]#DIV/0!
[/TD]
[TD]#DIV/0!
[/TD]
[TD]282
[/TD]
[TD]422
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Ba135
[/TD]
[TD]224
[/TD]
[TD]218
[/TD]
[TD]236
[/TD]
[TD]773
[/TD]
[TD]734
[/TD]
[TD]750
[/TD]
[TD]1088
[/TD]
[TD]1112
[/TD]
[TD]1116
[/TD]
[TD][/TD]
[TD]226
[/TD]
[TD]#DIV/0!
[/TD]
[TD]#DIV/0!
[/TD]
[TD]752
[/TD]
[TD]1105
[/TD]
[/TR]
</tbody>[/TABLE]



Stefano

 

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER

=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,M$4));$C10:$K10))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(SEARCH($M$4,$C$4:K$4)),$C10:$K10))

 

[StefanoM]

It does work for Column M but not for the N where I put "AB"

 

[Jonmo1]

It does work for Column M but not for the N where I put "AB"

Which response are you referring to, they are each slightly different.

And note, they are both ARRAY formulas that require special key stroke to enter correctly.
After typing the formula, DO NOT PRESS ENTER.
Instead, Press CTRL + SHIFT + ENTER
When done correctly, you will see the formula enclosed with {brackets}

 

[StefanoM]

Yes, the formula is correct with the brackets.
I'm referring to the response in the cell N10 that should be the average of the numbers 85, 87, 82, 284, 288, 273 (=183)

 

[Jonmo1]

I'm referring to the response in the cell N10 that should be the average of the numbers 85, 87, 82, 284, 288, 273 (=183)

I meant, were you referring to the formula I posted, or the one Aladin Posted ?

Anyway, it works fine for me.

Unknown
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	3												Mean Recovery (%)	Mean Recovery (%)	Mean Recovery (%)	Mean Recovery (%)	Mean Recovery (%)
4	4	Level	A	A	A	B	B	B	C	C	C		A	AB	ABC	B	C
5	5	Replicate	1	2	3	1	2	3	1	2	3
6	6	Condition
7	7	Lim Min
8	8	Lim Max
9	9	Analyte
10	10	Li7	85	87	82	284	288	273	423	435	409		85	183	263	282	422
11	11	Ba135	224	218	236	773	734	750	1088	1112	1116		226	489	695	752	1105
Sheet2

Cell Formulas
Range	Formula
M10	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,M$4)),$C10:$K10))}
M11	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,M$4)),$C11:$K11))}
N10	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,N$4)),$C10:$K10))}
N11	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,N$4)),$C11:$K11))}
O10	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,O$4)),$C10:$K10))}
O11	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,O$4)),$C11:$K11))}
P10	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,P$4)),$C10:$K10))}
P11	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,P$4)),$C11:$K11))}
Q10	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,Q$4)),$C10:$K10))}
Q11	{=AVERAGE(IF(ISNUMBER(SEARCH($C$4:$K$4,Q$4)),$C11:$K11))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[StefanoM]

Ok, I got it working, I was exchanging the order of the function search because you and Aladin posted quite the same formula and the correct one is yours.
I was wondering, if in your opinion, there is another way to do it without an array function?
Thank you a lot!

 

[Jonmo1]

Sure, try

=SUMPRODUCT(--(ISNUMBER(SEARCH($C$4:$K$4,M$4))),$C10:$K10)/SUMPRODUCT(--(ISNUMBER(SEARCH($C$4:$K$4,M$4))))

However, I wouldn't call this 'Better'. If anything it's likely much less efficient.
And it's technically still an array formula, it just doesn't require CTRL + SHIFT + ENTER.

 

[Aladin Akyurek]

Ok, I got it working, I was exchanging the order of the function search because you and Aladin posted quite the same formula and the correct one is yours.
I was wondering, if in your opinion, there is another way to do it without an array function?
Thank you a lot!

I had the search arguments switched. By the way, you need array processing with or without control+shift+enter.