# SumIf to a Specified Value

1. ## SumIf to a Specified Value

Wondering if there is a formula to do the following:

Add the Sales from column G up to the value in column B for a specific Person from column A. For example, I'd like to calculate Matt's sales by summing G3, and G6 up to the value from B2, and multiply their sales by their profit.
Handwork would be:

45*.75 + 55 * .7

The 65 comes from Matt's value of 100 - Matt's 1/22 sale for 45 - Matt's 1/21 sale for 74 (but that goes over 100 so only counting 55 of the 74)  Register To Reply

2. ## Re: SumIf to a Specified Value

65 + 45 = 110  Register To Reply

3. Originally Posted by TMS 65 + 45 = 110
Sorry shouldve said 45+ 55  Register To Reply

4. ## Re: SumIf to a Specified Value

Hello RossMo21 and Welcome to Excel Forum.
This proposal employs a helper column (I) which may be moved and/or hidden for aesthetic purposes.
The formula for the helper column is: =MAX(0,IF(SUMIFS(G\$2:G2,F\$2:F2,F2)<=INDEX(B\$2:B\$5,MATCH(F2,A\$2:A\$5,0)),G2,G2-(SUMIFS(G\$2:G2,F\$2:F2,F2)-INDEX(B\$2:B\$5,MATCH(F2,A\$2:A\$5,0)))))*H2
The formula for column C is: =SUMIFS(I\$2:I\$27,F\$2:F\$27,A2)
Let us know if you have any questions.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 