formula to work from data column
 

Hello

I have the following equation =AVERAGEIF(SUPPORT!K9:K99, "0")

support!K9:K99 being number of daydiff.

I want to select the daydiff values based on another date column, that only has the current month and year. ie Aug(8) & 2012

Regards

Rob

formula to work from data column
 

"masterdineen" wrote:
I have the following equation =AVERAGEIF(SUPPORT!K9:K99, "0")
support!K9:K99 being number of daydiff.
I want to select the daydiff values based on another date column,
that only has the current month and year. ie Aug(8) & 2012

=AVERAGEIFS(support!K9:K99,support!K9:K99,"0",
support!D9:D99,"="&DATE(2012,8,1),support!D9:D99, "<="&DATE(2012,8,31))

And in case there might be no qualifying cells:

=IFERROR(AVERAGEIFS(support!K9:K99,support!K9:K99, "0",
support!D9:D99,"="&DATE(2012,8,1),support!D9:D99, "<="&DATE(2012,8,31)),0)

Hi Rob,

If you're using Excel 2007 or later then you can use AVERAGEIFS() rather than AVERAGEIF() so you can include more criteria.

On the proviso the cell containing "Aug 2012" is a date formatted cell and actually reads as say 01/08/2012 behind the scenes you can use that as the start criteria and EOMONTH() against the same cell for the end criteria.

Hope that makes sense. Let me know if not and I'll see what I can do about writing the formula for you. You'll need to let me know which column the dates are in and which column holds the "Aug 2012" value.

S.