+ Reply to Thread
Results 1 to 4 of 4

SumIf to a Specified Value

  1. #1
    Registered User
    Join Date
    01-24-2020
    Location
    Kansas, United States
    MS-Off Ver
    2016
    Posts
    2

    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)
    Attached Files Attached Files
    Last edited by RossMo21; 01-24-2020 at 03:59 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,473

    Re: SumIf to a Specified Value

    65 + 45 = 110
    Trevor Shuttleworth - Excel Aid

    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


  3. #3
    Registered User
    Join Date
    01-24-2020
    Location
    Kansas, United States
    MS-Off Ver
    2016
    Posts
    2
    Quote Originally Posted by TMS View Post
    65 + 45 = 110
    Sorry should’ve said 45+ 55

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,146

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumif - Trying to figure out how to tell a sumif to look for multiple naming conventions
    By Typirious in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2017, 02:11 AM
  2. [SOLVED] SUMIF: don't understand that purpose of the OFFSET inside SUMIF
    By Vitalite in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-30-2017, 03:13 AM
  3. Replies: 2
    Last Post: 06-21-2017, 06:32 PM
  4. SumIf formula or Sumif with pivottable combined question
    By mcgleeuw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2015, 01:05 PM
  5. Subtracting from the SUMIF with the difference from the SUMIF range
    By iamblue91 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2014, 09:01 PM
  6. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  7. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1