AverageIFS based on partial text search

[AnilPullagura]

Hello Pros,

My mind is not co-operating with me today. Need help on this:

=AVERAGEIFS(F3:F161,C3:C161,">*Mentor*",F3:F161,"> 0%")

I want to average all cells in the range F3:F161 based on below conditions:

• Should ignore cells with zero values in the average range(F3:F161)
• Should ignore cells with the text "Mentor" in the range C3:C161

Quick help on this is required. Thanks in advance.

Anil

 

[Haseeb Avarakkan]

Hello, try this;

Change your formula to

=AVERAGEIFS(F3:F161,C3:C161,"<>*Mentor*",F3:F161,">0")

Or

=SUMIF(C3:C161,"<>*Mentor*",F3:F161)/MAX(1,COUNTIFS(C3:C161,"<>*Mentor*",F3:F161,">0"))

Or,

=AVERAGE(IF(1-ISNUMBER(SEARCH("Mentor",C3:C161)),IF(F3:F161>0,IF(ISNUMBER(F3:F161),F3:F161))))

Confirmed with CTRL+SHIFT+ENTER

 

[cgcamal]

Hi Anil,

If there are empty cells or words in the range, the function only count cells with numbers, as you can see below, 3 results are equal with 3 different average functions. But ignoring that it should look like this.

Excel Workbook
	F	G	H	I
3	2	37.4467532	37.4467532	37.4467532
4	3
5	17
6	3
7	4
8
9	7.4
10	7.8
11	8.2
12	8.6
13	9
14	9.4
15
16	10.2
17	Mentor
18	11
19	11.4
...

Cell Formulas
Range	Formula
G3	=AVERAGE(F3:F161)
H3	=AVERAGEIFS(F3:F161,F3:F161,">0",F3:F161,"<>*Mentor*")
I3	=AVERAGEIF(F3:F161,">0")

Regards