+ Reply to Thread
Results 1 to 2 of 2

Combined vlookup and sumifs in a single formula

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    1

    Combined vlookup and sumifs in a single formula

    Hello everyone,

    I just want to introduce myself. I am a new member of this forum. Being an intermediate Excel user, I joined this forum to hopefully learn from all the posts and improve my skills. Also I'm working on a major project right now involving lots of data management and analysis and I could really use some help on a formula involving vlookup and sumifs functions that I've been trying to create unsuccessfully.

    I have attached an spreadsheet example and I'm hoping if someone in the forum can help me.

    So from the spreadsheet, this is what I'm trying to do.
    1. in cell C3, I need a formula that will lookup date 1/25/2012 (in cell B3) in range H2:J7. I want this vlookup to do an exact match and an approximate match as well. So in this example, I want vlookup to return dates 1/25/2012 and 1/26/2012.

    2. Then, I want the formula to sum up the Amounts associated with Billed Item X. Those amounts should be associated with the dates returned in number 1 above. So the values should be 1,000 for 1/25/2012 and 500 for 1/26/2012. Therefore, if written correctly, I expect this formula to return a value of 1,500 in cell C3.

    3. Then I can just copy that formula for the rest of column C and adjust the parameter so that it would work for column D.

    Thank you in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Combined vlookup and sumifs in a single formula

    Try:

    =SUMPRODUCT(--(TEXT($I$3:$I$7,"mmmyy")=TEXT($B3,"mmmyy")),--($H$3:$H$7=LEFT(C$2)),$J$3:$J$7)

    copied down and to next column (D2 should say "Y Total Amount")
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ 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