Sum column based on value of row sum

I have a list of people that are required to complete 16 units. I need to be able to sum up how many units each person has completed and how many units towards the goal of 16 are completed each month. Once a person earns 16 units the additional units are not counted in the monthly total. The sums listed in B2:B4 will be actual earned units and can be any total value. I tried using sumif but may Range and Sum_range were different sizes so the calculation isnt accurate. Any help would be appreciated.


Jan Feb Mar Apr May Jun
Sam 18 4 4 4 4 2
Bill 22 16 2 2 2
Mark 20 20
sum 20 8 8 8 4 2


Desired Results

Jan Feb Mar Apr May Jun
Sam 18 4 4 4 4 2
Bill 22 16 2 2 2
Mark 20 20
sum 20 4 4 20 0 0

Let me try to explain my calculation logic based on my sample file with the table listed under Desired Results. Each person will have a total of all units earn in column B by adding all the values in their respective row. Cell B11=sum(C11:H11), that is the easy part. The issue I have is calculating the monthly accumulated, but each person can only contribute 16 units total to all monthly totals. With this sample population the total for each month in row 14 could never be greater than 48 (3 people x 16 units). In the column for Jan, Sam earns 4, and Bill earns 16, so the monthly total is 20. In Feb Sam earns 4, and bill earns 2, the monthly total only sums Sam 4 because Bill already reached the required amount of 16 the previous month. Any additional units Bill gets above 16 only get summed in B12, and they no longer effect the monthly sums in row 14. Likewise when you look at april, Sam gets 4, and Mark get 20. The monthly total in 20, because 4 units are given for Sam, and 16 to Mark. Mark can only contribute 16 to any month, and Sam has now completed his 16 unit in 4 months.

When summing the column values for the monthly total, I need to stop counting a row input when the row sum equals 16. Each row needs to contribute 16 units, and anything extra can't be counted in the monthly sum.

I got an equation to work, but it will get really large when I apply it to my data set of 104 people vice 3. Does anyone have any ideas how to streamline this equation, so I will be easier to work with. I have attached a updated file with the working equation.

=SUM(IF(16-SUM($C$3:D3)>=0,D3,IF(16-SUM($C$3:D3)<0,0,16-SUM($C$3:C3))),IF(16-SUM($C$4:D4)>=0,D4,IF(16-SUM($C$4:D4)<0,0,16-SUM($C$4:C4))),IF(16-SUM($C$5:D5)>=0,D5,IF(16-SUM($C$5:C5)<0,0,16-SUM($C$5:C5))))

I will repost the file tomorrow morning. I found an error with the equation and made some changes, but I don't have access to the file until I get back to work in the morning.

I could see the equations in the sample file after I enabled editing. Like I said I found an error in the calculation late yesterday and I up modified the equation to the following. This equation can be found in D6:H6.

=SUM(IF(SUM($C$3:C3)>16,0,IF(SUM($C$3:D3)<=16,D3,IF(SUM($C$3:D3)>16,16-SUM($C$3:C3),D3))),IF(SUM($C$4:C4)>16,0,IF(SUM($C$4:D4)<=16,D4,IF(SUM($C$4:D4)>16,16-SUM($C$4:C4),D4))),IF(SUM($C$5:C5)>16,0,IF(SUM($C$5:D5)<=16,D5,IF(SUM($C$5:D5)>16,16-SUM($C$5:C5),D5))))

Is it possible to move this question into the VBA section. I have been trying to figure out a way to make it work with equations, but maybe it requires vba or macro. any assistance would be greatly appreciated.