+ Reply to Thread
Results 1 to 3 of 3

Sum and SumIf together

Hybrid View

  1. #1
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Sum and SumIf together

    Hi all, I'm trying to sum four cells in second row (K2, L2, M2 & N2).
    Cell K2 worth 4 times, L3 worth 3 times, M2 worth 2 times, N2 worth 1 time.

    Then use SumIf depend on what text is in the target cell (H2).
    "High" value is multiply by 3, "Medium" multiply by 2, "Low" multiply by 1.

    Only half way through formula is working.

    This is the formula I'm using below and have attached sample.

    =((K2*4)+(L2*3)+(M2*2)+(N2*1))*(SUMIF(H2,"High",L17)+SUMIF(H2,"Medium",L18)+SUMIF(H2,"Low",L19))
    Thanks in advance.
    Attached Files Attached Files
    Last edited by unley; 10-16-2019 at 10:53 PM.
    I'm using MS Office 2013

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Sum and SumIf together

    If I understand you correctly, you want to sum your result based on the priority value? If so, give this formula a try. Using INDEX/MATCH you can first locate the index position based on a lookup value then reference the corresponded value based on a different column.
    =((K2*4)+(L2*3)+(M2*2)+(N2*1))*INDEX($L$17:$L$19,MATCH(H2,$K$17:$K$19,0))

  3. #3
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Re: Sum and SumIf together

    Perfect JieJenn, thank you for your help.
    I did not know that Index and match can do this trick.

+ 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. Nested SUMIF statement or multiple SUMIF's
    By Dan27 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2009, 06:55 AM

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