AVERAGEIF based on multiple 'OR' criteria in same column

[creative999]

Trying to do a AVERAGEIF calculation (col J) based on possible values (with wildcards) in the same column (col F). I know AVERAGEIF cant do a or, but any way around it?

=AVERAGE(IF((DSM!$F$2:$F$1000="*DRP*")+(DSM!$F$2:$F$1000="*STD*"),DSM!$J$2:$J$1000))

 

[Fluff]

Try

=AVERAGE(IF((ISNUMBER(FIND("DRP",DSM!$F$2:$F$1000)))+(ISNUMBER(FIND("STD",DSM!$F$2:$F$1000))),DSM!$J$2:$J$1000))

 

[Fluff]

Or a slightly shorter version.

=AVERAGE(IF(ISNUMBER(FIND({"STD","DRP"},DSM!$F$2:$F$1000)),DSM!$J$2:$J$1000))

 

[creative999]

Or a slightly shorter version.

=AVERAGE(IF(ISNUMBER(FIND({"STD","DRP"},DSM!$F$2:$F$1000)),DSM!$J$2:$J$1000))

If there is now 'DRP' value, i get a DIV error. If theres no 'STD' the average calculation still works.

 

[Fluff]

Are you trying that on 365 or 2019?

 

[creative999]

Are you trying that on 365 or 2019?

Office 365

 

[Fluff]

Hmm, it works for me with only STD

+Fluff 1.xlsm
	D	E	F				J
2			NY777439				1
3	14		NY777440				2
4			NY777441				3
5			STDE091296				4
6			STDK063719				5
7			STDK065738				6
8			STDE116305				7
9			STDK054727				8
10			STDK084589				9
11			STDK059739				10
12			STDK155760				11
13			STDJ978767				12
14			STDE071180				13
15			STDD953077				14
16			STDE123307				15
17			STDE048170				16
18			STDE071341				17
19			STDE069284				18
20			STDK056479				19
21
22			NY414249				21
23			STDO630791				22
24			STDD949204				23
25			NY905381				24
26			STDJ859571				25
27			STDE065262				26
DSM

Cell Formulas
Range	Formula
D3	D3	=AVERAGE(IF(ISNUMBER(FIND({"STD","DRP"},DSM!$F$2:$F$989)),DSM!$J$2:$J$989))

What about the 1st formula I posted?