+ Reply to Thread
Results 1 to 6 of 6

Help with =SUMIF or =IF

  1. #1
    Registered User
    Join Date
    07-09-2018
    Location
    vietnam
    MS-Off Ver
    10
    Posts
    3

    Help with =SUMIF or =IF

    Evening,

    I am trying to find a formula that auto-sums a value based on matching numbers in 2 seperate columns, if the number appears more than once the value multiples accordingly.

    Column A has only 18 random numbers A1:A18, Column B has 1-100 random numbers B1:B100, Column C has the $ value of 1-100 numbers in B1:B100, Column D1:D100 then sums C1:C100 based on matching numbers in A:A & B:B once, twice or however many.

    I tried =IF and it will SUM once but not any more a number shows up more than once in A1:A18.

    Thanks for any assistance
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Help with =SUMIF or =IF

    Change the 94 in B1 to general, not text. then use:

    =IF(ISNUMBER(MATCH(B1,$A$1:$A$18,0)),C1,"")
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    07-09-2018
    Location
    vietnam
    MS-Off Ver
    10
    Posts
    3

    Re: Help with =SUMIF or =IF

    Thanks Glenn for the quick response

    This formula is works great but the number 94 shows 3 times in A1:A18 how do I capture that so the total sum is $6 not just $2

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Help with =SUMIF or =IF

    That wasn't clear from col D, which I assumed was your expected answer.

    =IF(ISNUMBER(MATCH(B1,$A$1:$A$18,0)),C1*COUNTIF(A:A,B1),"")

  5. #5
    Registered User
    Join Date
    07-09-2018
    Location
    vietnam
    MS-Off Ver
    10
    Posts
    3

    Re: Help with =SUMIF or =IF

    Awesome Glenn thanks for that sorry about lack of clarity but much appreciated for your assistance

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Help with =SUMIF or =IF

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Replies: 4
    Last Post: 12-04-2014, 02:06 PM
  6. 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
  7. [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

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