+ Reply to Thread
Results 1 to 4 of 4

Sum numbers in a column if values in another column contain the number in another cell

  1. #1
    Registered User
    Join Date
    10-16-2020
    Location
    Los Angeles, CA
    MS-Off Ver
    16.42
    Posts
    2

    Sum numbers in a column if values in another column contain the number in another cell

    I've tried SUMIF, SUMIFS, SUMPRODUCT (with SUMIF) but the problem here seems to be CONTAINS. You can do this for text using wildcard ** but not numbers. How would I do this?

    Example:

    Sum column D if Column B (which are two sets of numbers) contains cell H5 (which is a number)

    THANK YOU IN ADVANCE!

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,026

    Re: Sum numbers in a column if values in another column contain the number in another cell

    Without sample, just guess:

    =SUMPRODUCT(ISNUMBER(SEACH(H5,B1:B100))*D1:D100)

    with 2 sets of number is in single cell in B1:B100, lookup number is in H5, sum value is in D1:D100

  3. #3
    Registered User
    Join Date
    10-16-2020
    Location
    Los Angeles, CA
    MS-Off Ver
    16.42
    Posts
    2

    Re: Sum numbers in a column if values in another column contain the number in another cell

    Thank you for your reply, but that did not work. I will include some screenshots.

    I actually have 2 workbooks. Workbook #1 contains the #'s (column H) I need to find on Workbook #2 column B. You can see in column B, there are two sets of numbers in each cell. So I need to find all cells in column B that contain the same number from Workbook #1's column H. Once I have found those, I need to sum the numbers in Workbook #2's column D that match Workbook #1's column H (H5, for example, and so on).

    Screen Shot 2020-10-17 at 11.15.48 AM.png

    Screen Shot 2020-10-17 at 11.15.35 AM.png

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,980

    Re: Sum numbers in a column if values in another column contain the number in another cell

    Hi and welcome to the forum

    Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

+ 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. Replies: 4
    Last Post: 10-09-2015, 05:19 AM
  2. Replies: 8
    Last Post: 11-18-2013, 04:24 PM
  3. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 4
    Last Post: 04-07-2012, 09:14 AM
  4. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel General
    Replies: 5
    Last Post: 04-06-2012, 12:02 PM
  5. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-06-2012, 11:19 AM
  6. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-06-2012, 11:18 AM
  7. [SOLVED] Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)
    By it_electronics2000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2012, 11:13 AM

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