+ Reply to Thread
Results 1 to 5 of 5

Sumifs across relative numbers of columns based on cell value.

  1. #1
    Registered User
    Join Date
    02-13-2020
    Location
    ATL
    MS-Off Ver
    10
    Posts
    5

    Exclamation Sumifs across relative numbers of columns based on cell value.

    So I'm working with a forecast for individual items. I need a way to enter a number into a cell (named 'timeframe'), and for the sum of a list of items to populate based on the 'timeframe' number of weeks (column).

    Each column is a week of data, and if 8 week is selected from a data validation, I need the first 8 columns to be sumif'd based on item numbers.

    I'm fairly sure it's going to involve some combo index / sumifs, and would like it to be fairly flexible to allow new items and columns to be added..

    Appreciate anyone who can help me with this.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Sumifs across relative numbers of columns based on cell value.

    So you want to sum from "Week 1" forward (based on the value in B1? and not to sum backwards from the last week (Week 15)?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-13-2020
    Location
    ATL
    MS-Off Ver
    10
    Posts
    5

    Re: Sumifs across relative numbers of columns based on cell value.

    Correct!

    A sum of demand by item, based on the amount of weeks I want to look at.

  4. #4
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    221

    Re: Sumifs across relative numbers of columns based on cell value.

    Worksheet "dash"

    B4=SUMPRODUCT((fcst!$A$2:$A$40=A4)*(fcst!$B$1:$P$1="Week "&$B$1)*(fcst!$B$2:$P$40)) copydown

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: Sumifs across relative numbers of columns based on cell value.

    In B4, try
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This assumes that weeks start from 1 and increment by 1, not skipping any. If that's not the case, I can modify.

+ 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] Creating a sumifs formula using relative reference and then copying as absolute to columns
    By SCarroll in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-09-2019, 08:41 AM
  2. Replies: 9
    Last Post: 01-25-2016, 04:26 PM
  3. [SOLVED] 'Sumifs' does not add up numbers based on given criteria
    By Tamarissa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-22-2015, 12:24 PM
  4. [SOLVED] Delete all rows and columns relative to starter cell.
    By smpita in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2014, 08:47 PM
  5. Countif based on relative cell
    By enphynity in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-19-2013, 11:43 AM
  6. [SOLVED] Sumifs or lookup or index/match based on columns and rows
    By ned0 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2013, 08:48 PM
  7. Inserting columns with numbers relative to previous data
    By LucyJ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-19-2012, 11:57 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