+ Reply to Thread
Results 1 to 9 of 9

Sum selected values in matrix based on date range as well as unique identifier in row

  1. #1
    Registered User
    Join Date
    07-08-2014
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    4

    Sum selected values in matrix based on date range as well as unique identifier in row

    Help please! I'm trying get a formula to apply to a whole matrix data set (as in the attached image) to sum all values in a date range AND a unique identifier for each column, i.e. LVA2

    E.g. Sum the LVA1 column values for dates 10/03/2009 to 16/03/2009

    Capture_excel.jpg

    Thanks in advance!

  2. #2
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    Hi SmanSyd

    I would be easier for you to upload a sample xlsx, rather than expect people to replicate all those rows and columns of data
    Pete
    Struggling Solutions Architect

  3. #3
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    Hi and welcome

    Please see attached and let me know if this is what you're after

    Sum selected smansyd v1.xlsx
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  4. #4
    Registered User
    Join Date
    07-08-2014
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    Thanks so much TheCman81, works perfect!! Exactly what I wanted

  5. #5
    Registered User
    Join Date
    07-08-2014
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    Update:

    I've just tried to apply the formula to a large range (see below) and am getting a '#VALUE!' error. Assuming there is a range max for SUMPRODUCT. Anyone know what I can do to make this work for large dataset?

    =SUMPRODUCT(--(A26:A2000>=G2)*(A26:A2000<=H2)*(B25:Z25=I2)*(B26:Z2000))

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    Quote Originally Posted by smansyd View Post

    =SUMPRODUCT(--(A26:A2000>=G2)*(A26:A2000<=H2)*(B25:Z25=I2)*(B26:Z2000))
    If there are any text entries in the sum range B26:Z2000 you'll get the #VALUE! error.

    Try this array formula**:

    =SUM(IF(A26:A2000>=G2,IF(A26:A2000<=H2,IF(B25:Z25=I2,IF(ISNUMBER(B26:Z2000),B26:Z2000)))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    07-08-2014
    Location
    Sydney, Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    Many thanks Tony, works a treat!

    Thank you both!!!!!!!!!!!!!!

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum selected values in matrix based on date range as well as unique identifier in row

    another
    =SUMIFS(INDEX(B2:E21,0,MATCH(I2,B1:E1,0)),A2:A21,">="&G2,A2:A21,"<="&H2)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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. Replies: 4
    Last Post: 05-22-2013, 03:23 PM
  2. [SOLVED] Copying the details of a code based on their unique identifier.
    By Jarvin24 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-17-2013, 07:32 AM
  3. [SOLVED] add increment identifier to unique values
    By hluk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2013, 04:44 PM
  4. [SOLVED] Counting a column of numbers based on a unique identifier
    By aaanenson in forum Excel General
    Replies: 2
    Last Post: 08-23-2012, 09:01 PM
  5. Replies: 5
    Last Post: 04-13-2012, 10:50 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