+ Reply to Thread
Results 1 to 5 of 5

Sum if + vlook up

  1. #1
    Registered User
    Join Date
    11-10-2019
    Location
    London, Englanf
    MS-Off Ver
    Office 365
    Posts
    2

    Sum if + vlook up

    Hi everyone,

    I'd be grateful if someone could lend their expertise on a sum if + vlook up formula. I have a report with weeks, 1-52, for multiple years listed vertically, with some data in an adjacent column. I'd like to create a formula to sum certain rows based on the periods the weeks relate to (for example, weeks 1-4 relate to period 1, so i'd want to only sum the rows for weeks 1-4)

    Sure, i can do a simple sum if formula, however to span weeks covering a number of years, it would involve quite a bit of manual manipulation to change the sum if statements to relate to the required period. I'd quite like to use a formula that i can roll down the weeks over the 4x years worth of weeks data i have.

    I was thinking i'd need a separate look up table to show what weeks belong to what period (i.e. wk1-4 = period 1), but can't quite get it to work

    Any help appreciated!
    Motley
    Attached Images Attached Images

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,135

    Re: Sum if + vlook up

    It would help if you attached a sample Excel workbook . Not a image.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Do not try to use the Paperclip icon, as this does not work on this forum.

    Hope this helps.

  3. #3
    Registered User
    Join Date
    11-10-2019
    Location
    London, Englanf
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Sum if + vlook up

    Hi Caracalla,

    OK, thanks for this piece of info. Attached is a sample file with dummy data in based on weeks of different years, then a tab for a look up with the periods the weeks relate to.
    I want to create a sum if / v look up that sums the weeks based on the period they relate to

    Huge thanks in advance
    Motley
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,135

    Re: Sum if + vlook up

    H2=SUMIFS($C$1:$C$1000,data!$B$1:$B$1000,">="&MINIFS('look up'!$A$2:$A$53,'look up'!$B$2:$B$53,data!E2),data!$B$1:$B$1000,"<="&MAXIFS('look up'!$A$2:$A$53,'look up'!$B$2:$B$53,data!E2),$A$1:$A$1000,F2)
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,678

    Re: Sum if + vlook up

    If you wanted to get a summary table of all the periods and years, you can use this formula in D1:

    =VLOOKUP(B1,'look up'!A:B,2)&"_"&LOOKUP(10000000,A$1:A1)

    then copy this down to the bottom of your list. Then with the years in cells F3 to H3, and the periods 1 to 12 in cells E4 to E15, you can use this formula in F4:

    =SUMIFS($C:$C,$D:$D,$E4&"_"&F$3)

    Copy this across and down as required.

    I've added some dummy data (in red) in column C to demonstrate this in the attached file, and corrected the final year in column A.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ 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. [SOLVED] VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value
    By onlyonekj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-29-2017, 03:30 PM
  2. Vlook up help
    By TrainerJ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2015, 11:01 AM
  3. [SOLVED] If and Vlook Up for the NA
    By dineshsachidananda in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-19-2015, 01:07 PM
  4. if and Vlook up help please
    By bimo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-20-2014, 12:48 PM
  5. Help fix my Vlook please
    By Mycotopian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2007, 04:59 AM
  6. vlook
    By dj_siek in forum Excel General
    Replies: 3
    Last Post: 04-10-2007, 12:44 AM
  7. vlook up
    By syed abbas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2007, 06:10 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