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)
Last edited by RossMo21; 01-24-2020 at 03:59 PM.
65 + 45 = 110
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Sorry should’ve said 45+ 55Originally Posted by TMS
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks