# Averageifs or 'sum/countif'??

#### kreineke

##### New Member
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

 HYDK Y 29-Apr-14 21-May-14 17 ALUB 8.5 1 Y 29-Apr-14 30-Apr-14 2 ALUB 8.5 1 Y 29-Apr-14 30-Apr-14 2 PEDB Y * 1-May-14 X #VALUE! HAMA Y 1-May-14 1-May-14 1 TOOL Y 1-May-14 1-May-14 1 PUNT Y 1-May-14 25-Aug-14 83 HYDK GSN Y 2-May-14 X #VALUE! HYDK Y 2-May-14 8-May-14 5 HYDK Y 2-May-14 8-May-14 X HYDK 8.5 Y 2-May-14 8-May-14 X

<tbody>
</tbody>

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
KLMNOPQRSTUVWX
11-May-14HYDKY29-Apr-1421-May-14171711
2HYDKALUB8.51Y29-Apr-1430-Apr-1422
3ALUB8.51Y29-Apr-1430-Apr-1422
4PEDBY*1-May-14X#VALUE!
5HAMAY1-May-141-May-14111
6TOOLY1-May-141-May-14111
7PUNTY1-May-1425-Aug-148383
8HYDKGSNY2-May-14X#VALUE!1
9HYDKY2-May-148-May-145511
10HYDKY2-May-148-May-14X11
11HYDK8.5Y2-May-148-May-14X11
12
13Average:11
Sheet30
Cell Formulas
RangeFormula
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,"")

Thanks to Skywriter's deciphering of your exhibit...
Rich (BB code):
``````=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)
``````

Thank you

Replies
1
Views
472
Replies
4
Views
173
Replies
3
Views
103
Replies
9
Views
339
Replies
1
Views
213

1,207,089
Messages
6,076,518
Members
446,211
Latest member
b306750

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back