Dynamic calculations based on Dates

[misjonara]

Hello everyone!

First of all I'd like to say hello as this is my first post on this forum! I am having a bit of an issue with my spreadsheet. I've created a document that tracks attendance (early leaves, no show etc), and each instance has assigned a letter to a numeric value. As an example if someone's late next to the date and his/hers name you'd see letter "L" and it will add .33 points to their "score"

Here are the instances and their values
M -> .33
L -> .33
E -> .33
A -> 1
T -> 1
N -> 1
PTO -> 0 (I'm adding it here since in the past I had an issue with PTO adding 1 point because of the letter T in it)

My goal for this spreadsheet is to have instances not counted if they occurred over a year ago. Also it is possible that 1 cell can get more than 1 instance. If someone's late and didn't punch I'd insert "M L" and it'd add up to .66.

So far my best formula is =SUMPRODUCT(ISNUMBER(FIND({"M";"E";"L";"A";"N";"T";"PTO"},C5:BL5))*($C$4:$BL$4>=EDATE(TODAY(),-12))*{0.33;0.33;0.33;1;1;1;1}), although it only calculates up to 1 instance per cell...

Any help would be appreciated as I've been stuck on this for some time now...

 

[Kaper]

My proposition is to:
a) use helper table (for instance in Sheet2)
b) use SUBSTITUTE to change confusing PTO into easily countable P
c) use SEARCH not FIND, because it's not case sensitive
d) use formula similar to your final proposition (but this seems to work correctly on sample data:

=SUMPRODUCT(ISNUMBER(SEARCH(Sheet2!$A$1:$A$7,SUBSTITUTE(C5:BL5,"PTO","P")))*Sheet2!$B$1:$B$7*($C$4:$BL$4>=EDATE(TODAY(),-12)))