Countif criteria for variable month/year but ONLY if 3rd value is latest, unique in-month

Hey guys,

I'm stumped -- I'm trying to count survey responses (6 or 7 ONLY on a scale of 1-7) in August 2013 (but as a cell referenced month & year) but 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

Count Criteria:
- If column J = either 6 or 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)
- If customer gives 2+ responses in August I want to count ONLY 6's & 7's, ONLY in August, and ONLY for the most recent August date in a descending list (FAIL)
- Do not count N/A's (FAIL)
- Do not count Blanks (success)

Here is the formula I've been using:
=SUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$100)=MONTH($C$2)),--(YEAR(Sheet1!$C$2:$C$100)=YEAR($C$2)),--((Sheet1!$J$2:$J$100)>5))

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 yellow.

ExcelForum - jpeateDeDupe Formula.xlsx

Thanks in advance for your help with this!!
jpeate

Originally Posted by Bernie Deitrick

The requirement that you ignore a specific month's previous value from a specific ID makes a single cell formula extremely complicated or even impossible, given the 'array of arrays of arrays' nature of the conditions. Helper column formulas can do it - and will 'just recalculate' as in the attached. This also addresses the problem (not shown in your example) of entirely duplicated data.

Attachment 341452

Even though this was not the answer I was looking for:
1) Wow, you are awesome and THANK YOU!!!
2) You are correct -- My ultimate goal is incredibly difficult - the difficult part is trying to limit down the list (mid-formula) before I count 6's and 7's to only contain August records and to only contain the 6 or 7 related to the first time the customer_id shows up in August.
3) If it helps, the dates will always be ordered from most recent date (at the top of the list) to the oldest date (at the bottom of the list) - so you can take the approach of A) taking the newest or two records, or B)the highest row value of two records ---- this is where I am stuck because I'm not seasoned enough to be able to do either of these. :/

If we can get this into one cell, I can't even tell you how much this would change the game for me.

Thanks,
jpeate

Oh my god, you did it!!! I can't believe you actually pulled this off - same day no less. YES, this is exactly what I wanted!

Did you invent excel? hahaha! I am super frickin' impressed! THANK YOU SKTNEER!

Quick Follow-up question: If I simply replace "SUM" with "AVERAGE" will it basically average those same values using the same logic or does that change the nature of the calculation due to how the other functions combine with this first function?

Awe-stickin,
jpeate

Sktneer, you rock man! This was exactly what I was looking for. All the calculations are coming through. I think their is some minimal rounding error occurring but thats the only downside.

Thanks for all you help!
jpeate

Will definitely add reputation. If I have a follow-on questions to a posts that is solved, what is the best way to handle it? e.g. I wanted to ask about some iteration on this or another formula can I just do so in this thread?

I'm slowly getting the logic behind this formula...

I want to be respectful of the forum rules and I'm kinda struggling to iterate on this since match and frequency are still kind of new to me...

Thank you!
jpeate

So this formula is what I was looking for but I have one last tweak if you're willing to take a look. If I wanted to change the command

FROM: "count 1 if J>5"

TO: "count 1if J>5, K>5 and L>5" (i.e. all three must be true to count 1)

...how would I amend the above formula?

Thanks,
jpeate

Big one ...
But Great..
sktneer..

Vikas Gautam

Wow. I've checked the numbers and its correct! This gave me exactly what I was looking for.

There are not a lot of people who can wrap their head around problems like this one - this was truly incredible to witness.

Thank you a thousand times!
jpeate

Hey Sktneer,

This formula for averaging is very close but its off by a little bit. The numerator of the average formula is 727 but the formula calculates 720 for some reason (off by 7). Please see the attached spread sheet.

I'm very new to arrays but excited to be learning. Can you help me get this formula over the finish line?

Thanks in advance!
John

excel formula close but a little off - please help.xlsx

Originally Posted by sktneer

You're welcome. Thanks for the feedback.
The suggested formula was for counting the instances meeting the criteria. To get the average ( I assume you want the average of col. J values), try the following Array Formula which requires CSE (Ctrl+Shift+Enter).

Please Login or Register  to view this content.

Hi Sktneer,

It looks like the isn't taking the MONTH portion of the formula into account (see attached). Looks like a few values in September are being calculated. Am I looking at this correctly?

array calculating wrong month values.xlsx

Thanks,
jpeate