+ Reply to Thread
Results 1 to 3 of 3

Vlookup and Sumif Combination

  1. #1
    Registered User
    Join Date
    07-28-2013
    Location
    Houston. Texas
    MS-Off Ver
    eXcel Mac
    Posts
    2

    Vlookup and Sumif Combination

    Hi Guys, I am really struggling with what may be a very simple question and was hoping you may be able to help. I am a OK with Excel but this has me stumped.

    Here is where i am at:

    I have a summary tab on my work book with several detailed tabs supporting it.
    The information on the detailed tabs is entered on specified dates.... but not necessarily in chronological order.
    The summary tab pulls the data from each supporting sheet and plugs it in to the relevant month


    So i have SUMIF looking up a date range (Month 1 less every greater than Month 1) to give the sum within a particular month... in this case July 2011.
    =SUMIF(Support!$C$15:$C$36,">="&DATE(2011,7,1),Support!$I$15:$I$36)-SUMIF(Support!$C$15:$C$36,">="&DATE(2011,8,1),Support!$I$15:$I$36)


    I will be linking the date range to a cell on the summary sheet so you can select the dates without having to adjust the formula.


    Here is what i want to add:

    Rather than just return everything for the month of July, i would like to be able to reduce (use vlookup?) the support tab down to categories (in this case properties) and then SUMIF by date range. Again I would like to link this to an input cell on the summary page so;

    Therefore on the summary I can enter in cell A1 the property name.... Say "House 1"
    Then I can enter a to (cell A2) and from (Cell A3) date.... Say "1-July-11" to "1-August-11"

    So I will get the summary of all the costs for House 1 between those dates.

    I really dont know the best way to do this?

    Cheers

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

    Re: Vlookup and Sumif Combination

    Hi and welcome to the forum

    From the sounds of it, all you need to do is change your sumif() function to a sumifS() function - it allows many criteria to be used. So for instance, you could sum a range between 2 dates AND for a specific category

    Give it a try and let me know how you make out?
    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

  3. #3
    Registered User
    Join Date
    07-28-2013
    Location
    Houston. Texas
    MS-Off Ver
    eXcel Mac
    Posts
    2

    Re: Vlookup and Sumif Combination

    You got it.... works spot on as follows:

    =SUMIFS(Security!I15:I34,Security!C15:C34,Summary!I2,Security!D15:D34,">="&DATE(2011,7,1),Security!D15:D34,"<"&DATE(2011,8,1))

    It seems so simple when you know.

    The only thing i am now having trouble with is change the date from nominated date to a cell reference so i can easily plug different date range...

    I actually want to plug a date in to say cell A2 and have that be the ">=" date and then have the "<" be the next month.

    Can you help with that one?

    Thanks again for your help.

+ 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. Desparate SUMIF and VLOOKUP combination?
    By vmansson in forum Excel General
    Replies: 2
    Last Post: 07-31-2012, 06:54 AM
  2. Tricky If & Sumif combination
    By Gti182 in forum Excel General
    Replies: 5
    Last Post: 10-29-2010, 05:02 AM
  3. SUMIF - VLOOKUP Combination
    By Fraser in forum Excel General
    Replies: 7
    Last Post: 10-27-2009, 12:34 PM
  4. Sumif and Vlookup combination?
    By Rhapsodie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2008, 12:26 AM
  5. SUMIF - HLOOKUP Combination
    By Mark in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2005, 04:06 PM

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