Complex sumif formula, may need array formula. Please help.

[MissCaseyS]

We are working on trying to allocate some overtime labor. There are multiple criteria that an item needs to meet in order to sum the overtime premium column, but I have been unable to come up with a good sumifs formula. I have a feeling I may need a more complex formula (to weed out duplicates), but am unsure of where to start.

Here is what I am trying to accomplish. I need to look at the overtime for each employee, per pay period, and if they only worked one event during that pay period, I need the sum of the overtime premium column. If they worked more than one event during the pay period, I need it to return a zero value. I hope this makes sense. Below is the sample data.

The only way I have somewhat come up with it is to put each pay period on a separate tab, with each employees name listed. This is not ideal. I then created sumifs to look first at the pay period in the data sheet, then look at the name then look at the pay code, and if it equals overtime to look at the event description. This is where I got stuck. How do I tell it that if the value in the Event description is not unique for the overtime in that pay period to not sum the overtime premium column???

Any help is GREATLY appreciated. I have spent hours on this already.

[TABLE="width: 748"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Payperiod[/TD]
[TD]Name[/TD]
[TD]Pay Code[/TD]
[TD]Overtime Premium[/TD]
[TD]Wages Less Premium[/TD]
[TD]Event Description
[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Robert J[/TD]
[TD]Overtime[/TD]
[TD]42.81[/TD]
[TD]85.74[/TD]
[TD]Series
[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Shirley A[/TD]
[TD]Overtime[/TD]
[TD]9.68[/TD]
[TD]19.38[/TD]
[TD]Administrative
[/TD]
[/TR]
[TR]
[TD]PP6
[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]16.48[/TD]
[TD]33.02[/TD]
[TD]Series
[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]16.48[/TD]
[TD]33.02[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]10.3[/TD]
[TD]20.64[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]74.18[/TD]
[TD]148.57[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]8.24[/TD]
[TD]16.51[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]45.33[/TD]
[TD]90.8[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]20.61[/TD]
[TD]41.27[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]8.24[/TD]
[TD]16.51[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]41.21[/TD]
[TD]82.54[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]20.61[/TD]
[TD]41.27[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] James E[/TD]
[TD]Overtime[/TD]
[TD]13.11[/TD]
[TD]26.27[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] James A[/TD]
[TD]Overtime[/TD]
[TD]4.12[/TD]
[TD]8.26[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] James A[/TD]
[TD]Overtime[/TD]
[TD]43.96[/TD]
[TD]88.04[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mathew M[/TD]
[TD]Overtime[/TD]
[TD]1.42[/TD]
[TD]2.84[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mathew M[/TD]
[TD]Overtime[/TD]
[TD]35.43[/TD]
[TD]70.98[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mathew M[/TD]
[TD]Overtime[/TD]
[TD]11.34[/TD]
[TD]22.71[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP6
[/TD]
[TD] Mathew M[/TD]
[TD]Overtime[/TD]
[TD]48.19[/TD]
[TD]96.52[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mathew M[/TD]
[TD]Overtime[/TD]
[TD]46.77[/TD]
[TD]93.69[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Charles W[/TD]
[TD]Overtime[/TD]
[TD]26.22[/TD]
[TD]52.53[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Charles W[/TD]
[TD]Overtime[/TD]
[TD]62.44[/TD]
[TD]125.06[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Shirley A[/TD]
[TD]Overtime[/TD]
[TD]19.36[/TD]
[TD]38.77[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Brant E[/TD]
[TD]Overtime[/TD]
[TD]31.47[/TD]
[TD]63.03[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Brant E[/TD]
[TD]Overtime[/TD]
[TD]41.96[/TD]
[TD]84.04[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Karen M[/TD]
[TD]Overtime[/TD]
[TD]57.14[/TD]
[TD]114.46[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Karen M[/TD]
[TD]Overtime[/TD]
[TD]114.29[/TD]
[TD]228.91[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]43.27[/TD]
[TD]86.67[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD]37.09[/TD]
[TD]74.29[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Dewanna K[/TD]
[TD]Overtime[/TD]
[TD]5.49[/TD]
[TD]11.01[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Dewanna K[/TD]
[TD]Overtime[/TD]
[TD]24.73[/TD]
[TD]49.52[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Jessie M[/TD]
[TD]Overtime[/TD]
[TD]21.71[/TD]
[TD]43.48[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Gary W[/TD]
[TD]Overtime[/TD]
[TD]13.99[/TD]
[TD]28.01[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] James E[/TD]
[TD]Overtime[/TD]
[TD]6.56[/TD]
[TD]13.13[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] James E[/TD]
[TD]Overtime[/TD]
[TD]66.87[/TD]
[TD]133.94[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Lander M[/TD]
[TD]Overtime[/TD]
[TD]15.98[/TD]
[TD]32.02[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] James A[/TD]
[TD]Overtime[/TD]
[TD]43.96[/TD]
[TD]88.04[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] James A[/TD]
[TD]Overtime[/TD]
[TD]43.96[/TD]
[TD]88.04[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Sarah E[/TD]
[TD]Overtime[/TD]
[TD]36.21[/TD]
[TD]72.54[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Linda[/TD]
[TD]Overtime[/TD]
[TD]7.99[/TD]
[TD]16.01[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Randy K[/TD]
[TD]Overtime[/TD]
[TD]9.4[/TD]
[TD]18.82[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP7[/TD]
[TD] Randy K[/TD]
[TD]Overtime[/TD]
[TD]76.52[/TD]
[TD]153.26[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP8[/TD]
[TD] Michael J[/TD]
[TD]Overtime[/TD]
[TD]3[/TD]
[TD]6.02[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP8[/TD]
[TD] Michael J[/TD]
[TD]Overtime[/TD]
[TD]57.06[/TD]
[TD]114.3[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP8[/TD]
[TD] Michael J[/TD]
[TD]Overtime[/TD]
[TD]36.04[/TD]
[TD]72.19[/TD]
[TD]Series[/TD]
[/TR]
[TR]
[TD]PP8[/TD]
[TD] Mike A[/TD]
[TD]Overtime[/TD]
[TD]23.33[/TD]
[TD]46.72[/TD]
[TD]Administrative[/TD]
[/TR]
[TR]
[TD]PP8[/TD]
[TD] Ana C[/TD]
[TD]Overtime[/TD]
[TD]25.85[/TD]
[TD]51.78[/TD]
[TD]Skywalk-Guest Services Position[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Would you specify what the expected outcome is and how it's hand-calculated?

 

[MissCaseyS]

For instance, for PP6 for Mario, he worked more than one event (Administrative and Series), so I would not want it to sum the Overtime Premium column. Otherwise, if I could find a formula that would even just say "yes" he only worked one event or "no" he worked multiple events for that pay period that would help greatly.

 

[Oeldere]

[TABLE="width: 329"]
<colgroup><col width="250" style="width: 188pt; mso-width-source: userset; mso-width-alt: 9142;"> <col width="188" style="width: 141pt; mso-width-source: userset; mso-width-alt: 6875;"> <tbody>[TR]
[TD="width: 250, bgcolor: transparent"]Pivot table can visialize this for you[/TD]
[TD="width: 188, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Rijlabels[/TD]
[TD]Coutn of Event Description[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PP6[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Charles W[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]James A[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]James E[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Mario G[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Administrative[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Mathew M[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Administrative[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Robert J[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Shirley A[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Administrative[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PP7[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Brant E[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Dewanna K[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Gary W[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]James A[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Administrative[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]James E[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Jessie M[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Karen M[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Lander M[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Linda[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Mario G[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Randy K[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Sarah E[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Shirley A[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Administrative[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PP8[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Ana C[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Skywalk-Guest Services Position[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Michael J[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Administrative[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Series[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Mike A[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Administrative[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]Eindtotaal[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Oeldere]

I found a way to determine it, but since i use an dutch excel version, and i don't want to to translate all formula's I'm willing to post my file on a free site. Please reply where I can post it. I used helpcolumns to determine the answer for the question. After that I used pivot table for visialization.

 

[Aladin Akyurek]

For instance, for PP6 for Mario, he worked more than one event (Administrative and Series), so I would not want it to sum the Overtime Premium column. Otherwise, if I could find a formula that would even just say "yes" he only worked one event or "no" he worked multiple events for that pay period that would help greatly.

pp6,mike,10,series
pp6,mike,15,Aministrative
pp7,mike,12,series
pp6,nadia,30,series

Suppose just the foregoing is the given, what are the results we are after?

 

[MissCaseyS]

Ok, maybe this is a more simple way to look at it. If we just look at pay period 6 and focus on these two employees, I have added a column with the results I wish to obtain. For each pay period, I need the formula to tell me by each employee if they had overtime did they only work the Series event. That is really the only event I care about. If they worked only the "Series" event during the pay period it would return a "yes" value, if they worked the "Series" event along with other events, the result would be "no".en

I just have no idea how to get it to first evaluate the data by pay period, then by employee, then decide if they only worked the "Series" event in that particular pay period.

[TABLE="width: 682"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD]Payperiod[/TD]
[TD]Name[/TD]
[TD]Pay Code[/TD]
[TD]Overtime Premium[/TD]
[TD]Wages Less Premium[/TD]
[TD]Event Description[/TD]
[TD]In particular pay period, only worked Series Event[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]16.48[/TD]
[TD="align: right"]33.02[/TD]
[TD]Series[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]16.48[/TD]
[TD="align: right"]33.02[/TD]
[TD]Administrative[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]10.3[/TD]
[TD="align: right"]20.64[/TD]
[TD]Series[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]74.18[/TD]
[TD="align: right"]148.57[/TD]
[TD]Series[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]8.24[/TD]
[TD="align: right"]16.51[/TD]
[TD]Administrative[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]45.33[/TD]
[TD="align: right"]90.8[/TD]
[TD]Series[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]20.61[/TD]
[TD="align: right"]41.27[/TD]
[TD]Series[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]8.24[/TD]
[TD="align: right"]16.51[/TD]
[TD]Administrative[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]41.21[/TD]
[TD="align: right"]82.54[/TD]
[TD]Series[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Mario G[/TD]
[TD]Overtime[/TD]
[TD="align: right"]20.61[/TD]
[TD="align: right"]41.27[/TD]
[TD]Series[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Charles [/TD]
[TD]Overtime[/TD]
[TD="align: right"]26.22[/TD]
[TD="align: right"]52.53[/TD]
[TD]Series[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]PP6[/TD]
[TD] Charles [/TD]
[TD]Overtime[/TD]
[TD="align: right"]62.44[/TD]
[TD="align: right"]125.06[/TD]
[TD]Series[/TD]
[TD]yes[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Is it possible to get an answer for the example I put up in message #6?

 

[MissCaseyS]

Is it possible to get an answer for the example I put up in message #6?

For PP6, each line for Mike would say "no" as he worked more than just the "Series" event. For PP7 for Mike, it would say "yes" as he just worked the "Series" event. Nadia would say "yes" in PP6 as she just worked the "Series" event.

 

[Aladin Akyurek]

For PP6, each line for Mike would say "no" as he worked more than just the "Series" event. For PP7 for Mike, it would say "yes" as he just worked the "Series" event. Nadia would say "yes" in PP6 as she just worked the "Series" event.

The following encodes no as 0 and yes as 1.
[TABLE="width: 255"]
<colgroup><col style="width: 48pt;" span="3" width="64"> <col style="width: 63pt; mso-width-source: userset; mso-width-alt: 2986;" width="84"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 84, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl63, bgcolor: transparent"]mike[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent"]series[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl63, bgcolor: transparent"]mike[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl63, bgcolor: transparent"]Aministrative[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp7[/TD]
[TD="class: xl63, bgcolor: transparent"]mike[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl63, bgcolor: transparent"]series[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl63, bgcolor: transparent"]nadia[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl63, bgcolor: transparent"]series[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


E2, control+shift+enter, not just enter, and copy down:

=(SUM(IF(FREQUENCY(IF($D$2:$D$5<>"",IF($A$2:$A$5=$A2,
  IF($B$2:$B$5=$B2,IF(ISNUMBER($C$2:$C$5),
  MATCH("~"&$D$2:$D$5,$D$2:$D$5&"",0))))),
  ROW($D$2:$D$5)-ROW($D$2)+1),1))=
 COUNTIFS($A$2:$A$5,$A2,$B$2:$B$5,$B2,$C$2:$C$5,"<>",$D$2:$D$5,"series"))+0

Not sure whether you wanted to include a testing of C2:C5...