Averageifs or 'sum/countif'??

[kreineke]

Hello,

I am trying to average values in a column based on 2 criteria of different ranges.

1st Criteria: is the adjacent cell a certain month.
2nd Criteria: does a different adjacent cell contain "text".

My problem: the column that I am averaging contains numbers, blanks, and text depending upon if the project has been complete. The issues that I have had in the past is using the MONTH function to ignore blanks and text without returning a "1". Now, I seems that blanks and text are causing a #DIV/0 error.

Question: Should I use AVERAGEIFS or SUM/COUNTIF....or something different.

Here is my 'failed' function:

=ROUNDUP(AVERAGEIFS(F09P1!U116:U400,F09P1!$M$116:$M$400,"=HYDK",F09P1!$U$116:$U$400,ISNUMBER,F09P1!$T$116:$T$400,"=MONTH(B4)"),1)

Here is a sample of the worksheet:

m | n | o | p | q | r | s | t | u

<s>HYDK</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>29-Apr-14</s>	<s>21-May-14</s>	17
<s>ALUB</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>8.5</s>	<s>1</s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>29-Apr-14</s>	<s>30-Apr-14</s>	2
<s>ALUB</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>8.5</s>	<s>1</s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>29-Apr-14</s>	<s>30-Apr-14</s>	2
PEDB				Y	*	1-May-14	X	#VALUE!
<s>HAMA</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>1-May-14</s>	<s>1-May-14</s>	1
<s>TOOL</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>1-May-14</s>	<s>1-May-14</s>	1
PUNT				Y		1-May-14	25-Aug-14	83
<s>HYDK</s>	<s>GSN</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>2-May-14</s>	<s>X</s>	#VALUE!
<s>HYDK</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>2-May-14</s>	<s>8-May-14</s>	5
<s>HYDK</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>2-May-14</s>	<s>8-May-14</s>	X
<s>HYDK</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>8.5</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>Y</s>	<s style="visibility: hidden; mso-ignore: visibility;"></s>	<s>2-May-14</s>	<s>8-May-14</s>	X

<tbody>
</tbody>

 

[skywriter]

The X in your T column and the X and #VALUE! in the U column are causing problems with a solution. The only thing I can come up with is some helper columns that you see in my example. You can always hide these columns if you don't want to see them.

Excel 2010
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	1-May-14		HYDK				Y		29-Apr-14	21-May-14	17	17	1	1
2	HYDK		ALUB		8.5	1	Y		29-Apr-14	30-Apr-14	2	2
3			ALUB		8.5	1	Y		29-Apr-14	30-Apr-14	2	2
4			PEDB				Y	*	1-May-14	X	#VALUE!
5			HAMA				Y		1-May-14	1-May-14	1	1	1
6			TOOL				Y		1-May-14	1-May-14	1	1	1
7			PUNT				Y		1-May-14	25-Aug-14	83	83
8			HYDK	GSN			Y		2-May-14	X	#VALUE!			1
9			HYDK				Y		2-May-14	8-May-14	5	5	1	1
10			HYDK				Y		2-May-14	8-May-14	X		1	1
11			HYDK		8.5		Y		2-May-14	8-May-14	X		1	1
12
13											Average:	11
Sheet30

Cell Formulas
Range	Formula
V1	=IF(ISNUMBER(U1),U1,"")
V13	=AVERAGEIFS(V1:V11,W1:W11,1,X1:X11,1)
W1	=IFERROR(IF(MONTH(T1)=MONTH($K$1),1,""),"")
X1	=IF(M1=$K$2,1,"")

 

[Aladin Akyurek]

Thanks to Skywriter's deciphering of your exhibit...

=AVERAGEIFS(
  $U$1:$U$11,
  $U$1:$U$11,"<"&9.99E+307,
  $T$1:$T$11,">="&$K$1,
  $T$1:$T$11,"<="&EOMONTH($K$1,0),
  $M$1:$M$11,$K$2)

 

[kreineke]

Thank you

 

[Aladin Akyurek]

Thank you

You are welcome.