Sum 1st 100 Units in 2 Columns & Sum Remainder

[Big Bird]

I have two columns of numbers (A & B). I need to calculate the first hundred units in both columns A & B. Then I need the sum of what is left over after the first hundred units. But I need to keep the totals in columns A & B separated (because ultimately they'll be multiplied by different dollar amounts). Is there a formula that will help me achieve these results? See example below:

	A	B
1	10	5
2	10	0
3	25	5
4	25	0
5	10	0
6	25	0
7	10	5
8	10	5
9	10	5

First hundred units in both columns A & B	90	10
Remaining units in both columns A & B	45	15

To be honest, I'm not even sure if a formula can achieve these results. We've been doing the calculation "manually" for a long time. Today, I thought I would try to see if anyone could help me out with formula to eliminate the time we spend calculating these numbers. Thank you in advance!

 

[JackDanIce]

Below assumes no headers and your data is in columns A and B, change output cells to needed and try:

C1 =SUMPRODUCT(A:A,--(ROW(A:A)<101))
C2 =SUMPRODUCT(A:A,--(ROW(A:A)>100))
D1 =SUMPRODUCT(B:B,--(ROW(B:B)<101))
D2 =SUMPRODUCT(B:B,--(ROW(B:B)>100))

Or enter 100 in say F1 and use:

C1 =SUMPRODUCT(A:A,--(ROW(A:A)<F1+1))
C2 =SUMPRODUCT(A:A,--(ROW(A:A)>F1))
D1 =SUMPRODUCT(B:B,--(ROW(B:B)<F1+1))
D2 =SUMPRODUCT(B:B,--(ROW(B:B)>F1))

 

[Big Bird]

Thank you, JackDanIce. Unfortunately, these formulas aren't giving me the results I want. C1 is giving me the sum total of all of the numbers in column A & D1 sum total of all numbers in column B in both formula variations you created - I think because all of values in both columns A & B are less than 101. C2 & D2 are giving me zero - I think because none of values in columns A & B are greater than 100.

I'm trying to find a formula to calculate the sum total of the first 100 units in both columns A & B regardless of how great or small the values are. I'm not sure how best to explain it - I'm worried I'm not using the right words to make things clear. This little table shows the results I want - which I hope will make it clearer.

First hundred units in both columns A & B	90	10
Remaining units in both columns A & B	45	15

 

[JackDanIce]

The suggested formulas:
Adds a value in column A or B, if the row number of that cell is < 101, i.e. 100 units
Similar logic for the other formula

Change all your values to 1 and then test and debug, it works for me what I put 1 in all cells A1:B120

 

[Big Bird]

I've been playing with your formulas, but still can't get them to achieve my goal. I think the trouble is the parameter of the formula is the rows - it's totaling everything in rows 1-100 and rows 101-120. I want a formula to total the values in the cells, but stop when the total gets to 100.

Maybe it will help if I explain what I'm doing - the values in column A are "regular hours" and the values in column B are "overtime hours." Each row is 1 day of work. (Ignore the fact that my table makes it look like people are working more than 24 hours in a day - they're not - I just used bigger numbers to get my point across with a smaller table). A client doesn't pay the first 100 hours worked, regardless of whether they are regular or overtime hours - they pay for any hours worked over and above 100 - but I still need to see the breakdown of the regular & overtime hours we eat vs. the regular & overtime hours the client pays for.

When I do this myself, I add the values in the cells in the following order: A1+B1+A2+B2+A3+B3...etc. Using the values in my table from my first post on this thread I add until I get to 100 or just past it: 10+5+10+0+25+5+25+0+10+0+25=115. I subtract the 15 because I want the first total to stop at 100. Then I add up the values in the A & B columns separately until the row where I got to 100 - in this case I went over 100 when I added the value in the A6 cell. For column A, I'll add A1 through A5 + part of A6 (10+10+25+25+10+25-15=90) and column B, I'll add B1 through B5 (5+0+5+0+0)=10. Next, I add the remaining values in the A column including the 15 I'd subtracted earlier to get an even 100 which is 15+10+10+10=45 and the B column 0+5+5+5=15. It's fairly simple arithmetic, but the order of events it what makes it complicated.

 

[JackDanIce]

I misinterrpreted 1st 100 units as 1st 100 cells which contain values, explanation above clarifies it's a running summation to consider, against a total sum value of 100.

I can't think of a solution without use of helper columns, whilst keeping formula simple, this is what I have done on a new spreadsheet:

A1 = 4, A2 =A1+1, Drag A2 to A200
B1 = A1+1, B2 = B1+1, Drag B2 to B200
C1 = SUM(A$1:A1), drag to C200 (this will create a sorted list of numbers for the 2nd argument used in MATCH function in E1)
D1 = SUM(B$1:B1), drag to D200
E1 = MATCH(100,$C$1:$C$200,1)
E2 = E1 + 1
F1 = MATCH(100,$D$1:$D$200,1)
F2 = F1 + 1

E1 and F1 show the row number that fits the criteria for the sum of values being <=100 for columns A and B respectively
E2 and F2 show the row number where the remainders begin for columns A and B respectively

 

[Big Bird]

Ah ha! Thank you, I see where you're going with this - I think I can work with this. Thank you, again!

 

[JackDanIce]

You’re welcome, glad it helps