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]

 

[MissCaseyS]

The following encodes no as 0 and yes as 1.
[TABLE="width: 255"]
<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...

Can you tell me what column C is for? The (10,15,12,30)? I don't have that on my source data. I would like to get rid of this column if possible, or find out what you were doing with it so I can use the formula you provided.

Thank you!!!!

 

[MissCaseyS]

The following encodes no as 0 and yes as 1.
[TABLE="width: 255"]
<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...

What is Column C for? I didn't have that on my original source data, and I am unsure of how you came up with it and if it is needed. (10,15,12,30). I tried this formula with the data given above and added a few lines for Nadia with the "Administrative" event as part of PP6, the formula then did not work. Am I missing something? I did control+shift+enter when on the formula line so it did have {} before and after.
Here is the formula:=(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

[TABLE="width: 320"]
<colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]mike[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]10[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]series[/TD]
[TD="class: xl66, width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]mike[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]15[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Aministrative[/TD]
[TD="class: xl66, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp7[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]mike[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]12[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]series[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]nadia[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]30[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]series[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]nadia[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]30[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Administrative[/TD]
[TD="class: xl66, align: right"]1



[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]pp6[/TD]
[TD="class: xl64, width: 64"]nadia[/TD]
[TD="class: xl65, width: 64"]30[/TD]
[TD="class: xl64, width: 64"]Administrative[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]pp6[/TD]
[TD="class: xl64, width: 64"]nadia[/TD]
[TD="class: xl65, width: 64"]30[/TD]
[TD="class: xl64, width: 64"]Administrative[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Can you tell me what column C is for? The (10,15,12,30)? I don't have that on my source data. I would like to get rid of this column if possible, or find out what you were doing with it so I can use the formula you provided.

Thank you!!!!

I thought you had such a range... In what follows, it's omitted.

What is Column C for? I didn't have that on my original source data, and I am unsure of how you came up with it and if it is needed. (10,15,12,30). I tried this formula with the data given above and added a few lines for Nadia with the "Administrative" event as part of PP6, the formula then did not work. Am I missing something? I did control+shift+enter when on the formula line so it did have {} before and after.
Here is the formula:=(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

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]mike
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]10
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]series
[/TD]
[TD="class: xl66, width: 64, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]mike
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]15
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Aministrative
[/TD]
[TD="class: xl66, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp7
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]mike
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]12
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]series
[/TD]
[TD="class: xl66, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]nadia
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]30
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]series
[/TD]
[TD="class: xl66, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]pp6
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]nadia
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]30
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Administrative
[/TD]
[TD="class: xl66, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]pp6
[/TD]
[TD="class: xl64, width: 64"]nadia
[/TD]
[TD="class: xl65, width: 64"]30
[/TD]
[TD="class: xl64, width: 64"]Administrative
[/TD]
[TD="class: xl66, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]pp6
[/TD]
[TD="class: xl64, width: 64"]nadia
[/TD]
[TD="class: xl65, width: 64"]30
[/TD]
[TD="class: xl64, width: 64"]Administrative
[/TD]
[TD="class: xl66, align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

The number range is deleted:

[TABLE="width: 235"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 122, 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"]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"]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"]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"]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"]nadia
[/TD]
[TD="class: xl63, bgcolor: transparent"]Administrative
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp6
[/TD]
[TD="class: xl63, bgcolor: transparent"]nadia
[/TD]
[TD="class: xl63, bgcolor: transparent"]Administrative
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp6
[/TD]
[TD="class: xl63, bgcolor: transparent"]nadia
[/TD]
[TD="class: xl63, bgcolor: transparent"]Administrative
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/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]
[/TR]
</tbody>[/TABLE]

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

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

 

[MissCaseyS]

I thought you had such a range... In what follows, it's omitted.



The number range is deleted:

[TABLE="width: 235"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 122, 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"]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"]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"]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"]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"]nadia[/TD]
[TD="class: xl63, bgcolor: transparent"]Administrative[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl63, bgcolor: transparent"]nadia[/TD]
[TD="class: xl63, bgcolor: transparent"]Administrative[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]pp6[/TD]
[TD="class: xl63, bgcolor: transparent"]nadia[/TD]
[TD="class: xl63, bgcolor: transparent"]Administrative[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/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]
[/TR]
</tbody>[/TABLE]

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

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

The formula seems to have an issue in that it is giving the incorrect result if there are more than one line in the pay period for Series. For instance, if all lines for Nadia said "series" it would put in a zero, instead of a 1. Since she only worked the "series", but on multiple days in the pay period, it should give me a value of 1.

Thank you so much for your responses so far!!!

 

[Aladin Akyurek]

The formula seems to have an issue in that it is giving the incorrect result if there are more than one line in the pay period for Series. For instance, if all lines for Nadia said "series" it would put in a zero, instead of a 1. Since she only worked the "series", but on multiple days in the pay period, it should give me a value of 1.

Thank you so much for your responses so far!!!

Great test...

Try instead:

Control+shift+enter...

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

 

[MissCaseyS]

It works! Thank you so much for your assistance! It is greatly appreciated!!!!!

 

[Aladin Akyurek]

It works! Thank you so much for your assistance! It is greatly appreciated!!!!!

You are welcome. Thanks for providing feedback.