+ Reply to Thread
Results 1 to 3 of 3

Formula to add up quantities depending on number of weeks

  1. #1
    Registered User
    Join Date
    06-20-2017
    Location
    Europe
    MS-Off Ver
    MS Office 2016
    Posts
    20

    Formula to add up quantities depending on number of weeks

    Hi all.

    I've been fighting with an Excel file all day and I am in need of help. I attache a simplified version of the file.

    There are 3 main factors: the number of weeks (displayed in Sheet 1), the quantity for each week (displayed in Sheet 2) and the number of the project.
    If the number of weeks needed is 4, then it must add up 4 columns from Sheet 2. If the number is 10, then it must add up 10 columns, and so on. So far, I've come up with the following formula:

    =SUM(OFFSET('Sheet2'!,$A$3:$A$31,0,1,1,$B25))

    Now, I want to match the number in the PN column with the one in Sheet 2 and as a result, obtain the sum that goes with each PN number. And here is where I request some help. I've tried with some VLOOKUP but I wasn't able to get anything.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Formula to add up quantities depending on number of weeks

    in F6

    =SUM(OFFSET('Sheet 2'!$A$2,MATCH($A6,'Sheet 2'!$A$3:$A$31,0),1,1,$B6))

    copy down
    Attached Files Attached Files
    Last edited by JohnTopley; 06-20-2017 at 11:36 AM.

  3. #3
    Registered User
    Join Date
    06-20-2017
    Location
    Europe
    MS-Off Ver
    MS Office 2016
    Posts
    20

    Re: Formula to add up quantities depending on number of weeks

    Thank you so much for your help John!!

+ 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] Add 12 weeks to date to return week number turn over at 52 weeks
    By nigelog in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2017, 05:58 AM
  2. [SOLVED] Formula for number of weeks in a month each year
    By Anarchus in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2017, 02:18 AM
  3. Formatting depending on value of other cell (date being within two weeks)
    By karen1978 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2015, 08:10 AM
  4. Formula to work out a sum depending on number of weeks
    By malrobarts in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-13-2014, 08:23 AM
  5. [SOLVED] Count number of holidays depending on number of weeks
    By ANTH16 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2013, 03:42 AM
  6. XL IF formula to calculate Number of weeks based on dates in Column:
    By sancy.jobs@gmail.com in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-15-2012, 07:38 AM
  7. Replies: 2
    Last Post: 12-01-2009, 01:57 PM

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