Averageif criteria for variable mon/yr but ONLY if 3rd value is latest, unique in-month

Hey guys,

I'm struggling -- I'm trying to average all survey responses (numbers ONLY on a scale of 1-7) in August 2013 (as a cell referenced month & year) and I need to add a month specific de-dupe to the formula. I need to do all this in one formula with no helper columns such that I can change the month to July 2013 and everything will just recalculate.

I have the following data per column:
Column C = Date
Column J = How a customer rates shipping speed on a scale of 1-7
Column AB = Customer ID

AVERAGE Criteria:
- If column J = 1,2,3,4,5,6,7 (success)
- If the date in column C is the same month as the date in C2 (success)
- If the date in column C is the same year as the date in C2 (success)
- Do not average N/A's (?)
- Do not average Blanks (?)
- If customer gives 2+ responses I only want to include the most recent one in the averagein August I want to average ONLY the numbered values (1 thru 7), ONLY in August, and ONLY for the most recent August date in a descending list from -- that is, newest to oldest (FAIL)


Here is the formula I've been using:
=ROUND((AVERAGEIFS(Sheet1!$J$1:$J$15000,Sheet1!$C$1:$C$15000,">"&EOMONTH($C$2,-1),Sheet1!$C$1:$C$15000,"<="&EOMONTH($C$2,0))),2)

I've attached a XLSX file for reference and I highlighted duplicates Customer IDs in red and the rows actually I want to count in green.

ExcelForum - jpeateDeDupe MEAN Formula-3.xlsx

Thanks in advance for your help with this!!
jpeate

sktneer. figured this one out-- see formula below:

Formula:

Please Login or Register  to view this content.

http://www.excelforum.com/excel-form...ml#post3814686