Formula to calculate unique values for an employee

[irishguurl]

For each employee, I need to look at a specific course and check to see if its expired. If its expired then they need to complete that course plus an additional course. All I need is a count of the courses that need to be completed for this scenario. Maybe I am over thinking it, but I can't seem to get a formula working...help!!!!!

[loginjmor]

Hi -

Can you upload a sample spreadsheet so we can help develop some solutions for you?

Under the reply area is a button called Go Advanced. You can use the little paper clip tool in the tool bar to attach a spreadsheet.

[irishguurl]

User ID Suite Name Course Test Taken Date Score Status
*AC2 WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 12/27/2012 100 Mastered
*AC2 WORKPLACE VIOLENCE Workplace Violence (Video) 12/15/2011 91 Mastered
*ARG WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 8/28/2012 100 Mastered
*ARG WORKPLACE VIOLENCE Workplace Violence (Video) 5/12/2013 73 Mastered
*BJP3 WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event Started
*BJP3 WORKPLACE VIOLENCE Workplace Violence (Video) 6/1/2011 73 Mastered
*BRV WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event Not Started
*BRV WORKPLACE VIOLENCE Workplace Violence (Video) 8/5/2011 82 Mastered
*CEH WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 9/19/2012 100 Mastered
*CEH WORKPLACE VIOLENCE Workplace Violence (Video) 2/7/2013 82 Mastered
*CJW2 WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 2/5/2013 100 Mastered
*CJW2 WORKPLACE VIOLENCE Workplace Violence (Video) 6/14/2011 91 Mastered
*DAZ WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 2/21/2013 100 Mastered
*DAZ WORKPLACE VIOLENCE Workplace Violence (Video) 7/16/2012 73 Mastered
*DCD WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 8/27/2012 100 Mastered
*DCD WORKPLACE VIOLENCE Workplace Violence (Video) 6/3/2011 82 Mastered
*DEW WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event Not Started
*DEW WORKPLACE VIOLENCE Workplace Violence (Video) Expired
*DJG2 WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 8/27/2012 100 Mastered
*DJG2 WORKPLACE VIOLENCE Workplace Violence (Video) 6/26/2012 82 Mastered
*DLB8 WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event Not Started
*DLB8 WORKPLACE VIOLENCE Workplace Violence (Video) 2/19/2013 82 Mastered
*DMO WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 1/7/2013 100 Mastered
*DMO WORKPLACE VIOLENCE Workplace Violence (Video) 8/21/2012 73 Mastered
*DTP WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 11/29/2012 100 Mastered
*DTP WORKPLACE VIOLENCE Workplace Violence (Video) 11/29/2012 100 Mastered
*EDM WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 8/30/2012 100 Mastered
*EDM WORKPLACE VIOLENCE Workplace Violence (Video) 11/11/2012 73 Mastered
*EPC WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event 9/24/2012 100 Mastered
*EPC WORKPLACE VIOLENCE Workplace Violence (Video) 5/9/2013 73 Mastered
*ERH WORKPLACE VIOLENCE Run, Hide, Fight Surviving an Active Shooter Event Not Started
*ERH WORKPLACE VIOLENCE Workplace Violence (Video) Expired


So for every user, if the Workplace Violence (Video) status is expired, then they have to take that class plus the Run, Hide, Fight... class no matter what the status of Run, Hide, Fight... class is. In this particular set of data my count should be 4, 2 for user ERH and 2 for DEW. If the Workplace Violence (Video) status is Mastered and the Run, Hide, Fight... status <> Mastered, then the user doesn't have to take the Run, Hide, Fight... Basically, the only time you take the Run, Hide, Fight class is when the Workplace Violence (Video) is expired. Thanks for the help!

[irishguurl]

I can't seem to upload a file at the moment.

[loginjmor]

Hi -

Try the attached spreadsheet. I had a little trouble importing your data from text into a spreadsheet, but I think I got it right.

Anyway, there are two formulas. The second formula checks to see if the video is expired, if it is, it puts a 1 in the Retake Course column. The fisrt formula simply checks to see if there is a 1 under the following cell and if so, places a 1 in its own cell. The results appear to be what you are looking for.

Hope this helps.

[irishguurl]

Here it is! :-)

[irishguurl]

Hmmmm...not exactly what I was thinking, but it might work! Could you set it up so that it keep a running count instead of putting a 1 in a column and running a total at the end?

[loginjmor]

Hi -

Sorry for slow response. Got caught up in my day job. Attached is your spreadsheet with a running total of the expired video. You can multiply this number by two to get the total number of courses to take (you can modify the formula to multiply the result by 2). Hope this is what you are looking for.