+ Reply to Thread
Results 1 to 8 of 8

SUMIF - VLOOKUP Combination

  1. #1
    Registered User
    Join Date
    10-27-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    SUMIF - VLOOKUP Combination

    Hi,

    I am sitting at work trying to build myself a spread sheet. I have my raw data, which I am trying to put into a cash flow template I am trying to automate.

    For income below are some of the categories:
    Week Number 1 2 3 4 5 6
    Rent 50,000
    Service Charge 10,000
    Events
    Exhibitions 20,000
    Development
    Other


    I can line up all the totals for the categories by using SUMIF.
    e.g: =SUMIF('Year Data'!$U$2:$U$5212,C14,'Year Data'!$O$2:$O$5212)


    I then need to split it out over which week it has accured in ans using this forumla can get this:=VLOOKUP(F8,'Year Data'!S2:V5212,4,FALSE)

    Week number12345
    Rent10,000
    Service Charge 10,000
    Events
    Exhibitions 20,000


    What I want is to combine the two SUMIF / VLOOKUP and get it looking like this?
    (Like this if possible)
    Week number 1 2 3 4 5 6
    Rent 10,000 5,000 20,000 15,000
    Service Charge 2,5000 2,5000 2,5000 2,5000
    Events
    Exhibitions 10,000 10,000
    Any help will be much appreciated.

    Cheers
    Fraser

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

    Re: SUMIF - VLOOKUP Combination

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    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.

  3. #3
    Registered User
    Join Date
    10-27-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: SUMIF - VLOOKUP Combination

    Please see the spread sheet attached - any help much appreciated.
    Cheers
    Fraser
    Attached Files Attached Files

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

    Re: SUMIF - VLOOKUP Combination

    I am not sure I understand.

    Is the results you show the intended results?

    Because for week 16 on Rent, it looks like the answer should be 645 not -1000.

    Please let me know what the intended results in the columns are to be.

  5. #5
    Registered User
    Join Date
    10-27-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: SUMIF - VLOOKUP Combination

    NBVC - Thank you for your patients!

    Your help is greatly appreciated.

    OK updated spread sheet is attached.

    Requirements:
    Currently trying to set up weekly cash flow spread sheet
    I download info from SAGE - "year data", and need to allocate info to this spread sheet.
    I need to put the actuals in the correct line e.g rent, and under the correct week.
    Please note - the "year data" has been simplified to one transaction per week, per category, when there will be actually 100's of transaction per week for any one category.

    If I can get this solved - it would be amazing.

    I then want to pull the actuals through to the summary page. the summary page will be actuals up to the week in box C7, and then forecast as defined on the forecast tab after that. Would you use a Hlookup for this?

    many Thanks
    Fraser
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-25-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    6

    Re: SUMIF - VLOOKUP Combination

    Please find attached your spreadsheet with the right formulae (see on the light grey background). Note that this is an array formula, so need to press Ctrl + Shift + Enter when entering it.
    Attached Files Attached Files

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

    Re: SUMIF - VLOOKUP Combination

    In E13:

    =SUMPRODUCT(--('Year Data'!$F$2:$F$519=$C13),--('Year Data'!$D$2:$D$519=E$7),'Year Data'!$C$2:$C$519)

    copied down and across

  8. #8
    Registered User
    Join Date
    10-27-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: SUMIF - VLOOKUP Combination

    Igorsp7 - Thanks a million.

    Will be my bed time reading to get it completed tonight.

    It is great to find such great help and support.

    Cheers
    Fraser

+ 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