• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing after a number of instances

J

Joseph.Craen

New Member
Hello and thank you for taking a moment to look over my issue (this is my first post on this forum).

I'm currently working on an absence schedule that will work on a rolling 12 month calendar. The premise is that different infractions will have different valuations (points); these valuations will be totaled throughout the rolling 12 months, resulting in varying levels of discipline based upon predetermined thresholds. It was also determined that there should be an allowable number of infractions before employees would begin to accrue points. This is where my issue comes in to play, I have it set to have a valuation of zero using IFS((SUM(COUNTIF(C7:AG7,"UL"),(COUNTIF(C7:AG7,"OL")))<=3),0 and to sum points when above three instances ,(SUM(COUNTIF(C7:AG7,"UL"),(COUNTIF(C7:AG7,"OL")))>3),SUM(COUNTIF(C7:AG7,"OL")*1,COUNTIF(C7:AG7,"UL")*0.5)). In this instance the valuations for the "UL" and "OL" code is .5 and 1 respectively; the issue is the formula sums all instances up to that point, I need it to exclude the first three instances (three is going to be the allowable tolerance before points begin to be accrued).

Thank you for any help you might be able to provide on this (whether it be aiding in solving this issue or having an alternative method or formula). I'll also include the actual file in case anyone wants/needs to look at it (I only included January because the entire file is too large). The formula I'm referring to is located in cell AH8.
 

Attachments

  • Employee absence schedule 2021 excerpt.xlsx
    200.6 KB · Views: 7
If you have 365 with the LET function how about
Code: 
=LET(Rng,C7:AG7,Qty,SUM(COUNTIF(Rng,{"UL","OL"})),Late,INDEX(FILTER(Rng,(Rng="UL")+(Rng="ol")),SEQUENCE(Qty-3,,4)),IF(Qty<=3,0,SUMPRODUCT(((Late="ul")*0.5)+(Late="ol")*1)))
 
Ok, if you don't have 365 this works for Xl 2013 onwards
Code: 
=SUM(IFERROR(ROWS(FILTERXML("<l><m>"&TEXTJOIN("</m><m>",,IF((C7:AG7="ul")+(C7:AG7="Ol"),C7:AG7,""))&"</m></l>","//m[position()>3][contains(.,'ul')]")),0)*0.5,IFERROR(ROWS(FILTERXML("<l><m>"&TEXTJOIN("</m><m>",,IF((C7:AG7="ul")+(C7:AG7="Ol"),C7:AG7,""))&"</m></l>","//m[position()>3][contains(.,'ol')]")),0))
 
Fluff13 said:
Ok, if you don't have 365 this works for Xl 2013 onwards
Code: 
=SUM(IFERROR(ROWS(FILTERXML("<l><m>"&TEXTJOIN("</m><m>",,IF((C7:AG7="ul")+(C7:AG7="Ol"),C7:AG7,""))&"</m></l>","//m[position()>3][contains(.,'ul')]")),0)*0.5,IFERROR(ROWS(FILTERXML("<l><m>"&TEXTJOIN("</m><m>",,IF((C7:AG7="ul")+(C7:AG7="Ol"),C7:AG7,""))&"</m></l>","//m[position()>3][contains(.,'ol')]")),0))
Click to expand...
I doesn't look like I can use the Let function, but the second formula seems to do the trick. Thank you! I really do appreciate it.

I am still rather new to excel; would it be too much trouble to ask for a bit of a breakdown on how this works? Mainly the ROWS, FILTERXML, and &TEXTJOIN functions. If not, its not a big deal. I'm sure with some time and research I can figure it out. If you can, I appreciate it, but if not, I still offer my deepest gratitude for your help with this. I've already spent so long on this and I don't know how long it would have taken me to figure it out (if ever). Thanks again!
 
As the Filterxml function is case sensitive this might be a better version
JavaScript: 
=SUM(IFERROR(ROWS(FILTERXML("<l><m>"&LOWER(TEXTJOIN("</m><m>",,IF((C7:AG7="ul")+(C7:AG7="Ol"),C7:AG7,"")))&"</m></l>","//m[position()>3][contains(.,'ul')]")),0)*0.5,IFERROR(ROWS(FILTERXML("<l><m>"&LOWER(TEXTJOIN("</m><m>",,IF((C7:AG7="ul")+(C7:AG7="Ol"),C7:AG7,"")))&"</m></l>","//m[position()>3][contains(.,'ol')]")),0))

The textjoin function concatenates all cells that have either ul or ol using the "</m><m>" as the separator. so you get a string like
"<l><m>ol</m><m>ol</m><m>ol</m><m>ul</m><m>ol</m><m>ul</m></l>"
the filterxml then filters that with //m[position()>3][contains(.,'ul')] which is values of ul after the 3rd value in the string & returns an array of
{"ul";"ul"}
The rows function then returns how many rows there are in the array, in the above case 2.

Hope that helps
 
I applied the updated formula and it works perfectly. I didn't know it was case sensitive, so thank you for that; I'm positive that would have come up at some point. I think I'll have to work with it to completely understand it, but that does help.

I have one last question. How would I get this to function across multiple months? Ultimately, this allowance should stretch across a twelve month period. So if an employee has one tardy in January, one in February, one in May, and one in July, they would begin receiving points for tardies from July onward. If that's too tall an order I'm sure we can come up with a workaround. Again I want to thank you for helping me this far.
 
Ouch, you would be better of with all of it on one tab.
To do it over multiple tabs you would need to do
Code: 
=SUM(IFERROR(ROWS(FILTERXML("<l><m>"&LOWER(TEXTJOIN("</m><m>",,IF((January!C7:AG7="ul")+(January!C7:AG7="ol"),January!C7:AG7,""),IF((February!C7:AG7="ul")+(February!C7:AG7="ol"),February!C7:AG7,"")))&"</m></l>","//m[position()>3][contains(.,'ul')]")),0)*0.5,IFERROR(ROWS(FILTERXML("<l><m>"&LOWER(TEXTJOIN("</m><m>",,IF((January!C7:AG7="ul")+(January!C7:AG7="ol"),January!C7:AG7,""),IF((February!C7:AG7="ul")+(February!C7:AG7="ol"),February!C7:AG7,"")))&"</m></l>","//m[position()>3][contains(.,'ol')]")),0))
and add the other sheets in the same way.
 
You must log in or register to reply here.
Back
Top