+ Reply to Thread
Results 1 to 16 of 16

SUMIF And VLOOKUP

  1. #1
    Registered User
    Join Date
    08-15-2006
    Posts
    71

    SUMIF And VLOOKUP

    Hi,

    I set up a SUMIF forumula shown below;

    Please note that the formula is set up in "Data Table 2":

    "=SUMIF('Data Table 1'!E5:E100,A3,'Data Table 1'!G5:G100)",

    where within column E - cell 5 to 100, the criteria in cell A3 (in Data Table 2) is searched for and then adds all the numberical values with in column G - cell 5 - 100 (in Data Table 1) that correspond to the criteria in cell A3.

    This formula works well for what I needed, but I need somthing a little different for my next project.

    I need to search for a criteria in a column A, once I have found all the cells (there may be more than one cell that meets the criteria) that meet the criteria, I need to find in column B all the cells that have the word "Incomplete" associated within the row that meets the criteria. So...is there a way I can combine SUMIF and VLOOKUP formulas to do this.

    Thanks in advance for any help

    Calli

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try sumproduct

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Something like

    =SUMPRODUCT((A1:A2000="Criteria1")*(B1:B2000="Incomplete"))

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    08-15-2006
    Posts
    71
    Hi,

    In the formula you gave me, can the "Criteria 1" be a reference cell, so the formula would like the following:

    =SUMPRODUCT((A1:A2000="A3")*(B1:B2000="Incomplete"))

    Is this possible?

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Yes you can do it that way but you don't need the quotes around the reference cell (otherwise "A3" will be interpreted as the literal text "A3" rather than the contents of that cell) so use:

    =SUMPRODUCT((A1:A2000=A3)*(B1:B2000="Incomplete"))

  5. #5
    Registered User
    Join Date
    08-15-2006
    Posts
    71
    Hey,

    thanks alot it worked, but still have one more question. In my data table, I have the words "Incomplete" followed by some commentary (details of the issue). Is there away to incorporate the "FIND" formula in to the equation so that its just looking for the word "Incomplete" despite what text follows it.

    Calli

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try

    =SUMPRODUCT((A1:A2000=A3)*(ISNUMBER(SEARCH("Incomplete",B1:B2000))))

    VBA Noob

  7. #7
    Registered User
    Join Date
    08-15-2006
    Posts
    71
    thanks it worked

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Glad you found a solution

    VBA Noob

  9. #9
    Registered User
    Join Date
    10-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: SUMIF And VLOOKUP

    I have a similar problem. I want a value to be associated with A, B, and C. Then I want to type either A, B, or C into a cell, and have Excel spit out the sum of the values mentioned in that sell... I searched sumif vloockup, but I'm not sure this will do it or not...

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: SUMIF And VLOOKUP

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

  11. #11
    Registered User
    Join Date
    04-06-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: SUMIF And VLOOKUP

    Hello,

    Thanks for your help in advance!

    Let's say, that in row A1:A10 you have numbers from 1 to 10
    In Cell B1, you calculate average of the Range A1:A10
    In Cell C1, you would like to SUM all the numbers from Range A1:A10 (this is a evaluation and sum range, to be more exact) that are greater then average value in B1!
    Formula =sumif(A1:A10;">B1";A1:A10) is not working, since Excel "seas" the formula in Criteria field with "" meaning, that Criteria searches B1 or even >B1 as a string in A1:A10 range.
    If Instead of B1, I enter a value, which is a result displayed in B1 Cell, than it works fine (eg: =sumif(A1:A10;">4,52";A1:A10)) but this is not a solution, since the value in B1 could be dynamic and I don't want to change sumif formula every time, value in Cell B1 in changed.

    BR

  12. #12
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: SUMIF And VLOOKUP

    see my previous reply

    Quote Originally Posted by daddylonglegs View Post
    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Audere est facere

  13. #13
    Registered User
    Join Date
    04-06-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: SUMIF And VLOOKUP

    Thanks, got it.

  14. #14
    Registered User
    Join Date
    09-03-2015
    Location
    London, England
    MS-Off Ver
    excel 2010
    Posts
    5

    Re: SUMIF And VLOOKUP

    thanks it worked

  15. #15
    Registered User
    Join Date
    09-07-2015
    Location
    Tucson, AZ
    MS-Off Ver
    2013
    Posts
    1

    Re: SUMIF And VLOOKUP

    I am not sure if this the correct formula set:

    I want A3 to look up [search] A20:A30 and if it matches [in this case a name] then put into B3 the corresponding value in B20:B30 that is next to the name.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,772

    Re: SUMIF And VLOOKUP

    dmbedge, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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