Consulting

Results 1 to 9 of 9

Thread: How to use SUMPRODUCT for text offset over a discontiguous range? [Tutorial]

  1. #1
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location

    How to use SUMPRODUCT for text offset over a discontiguous range? [Tutorial]

    How do you successfully calculate multiple ranges and criteria using
    SUMPRODUCT, i have just started using SUMPRODUCT on a regular basis and starting to
    understand the power of using it, i do find difficulty calculating figures for an offset
    of text over non contiguous ranges is there an easy way to achieve it or remember how to
    formulate it?

    This will be the base question for an ongoing tutorial kindly offered by xld, for those of you that don't know xld (www.xldynamics.com) has written the definitive tutorial and example uses around SUMPRODUCT rather than using array formulae that need to be committed with Ctrl+Shift+Enter.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Simon,

    It depends how non-contiguous the range is.

    If it is every nth column (or row) it is very simple. Then you SUMPRODUCT across all columns (rows) and use the MOD function to isolate the columns (rows) that you are interested.

    For instance, let us assume that the range to be tested is A2:Z2, and we have say a date, then a name, then an amount, you can sum the amounts for say Simon like so

    =SUMPRODUCT(--(MOD(COLUMN(A2:Z2),3)=2),--(A2:Z2="Simon"),B2:AA2)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location
    On the same site, there is also one of the Excel's articles that I most like: http://www.xldynamic.com/source/xld.LastValue.html

    It is about different ways of finding last value/text in column by formulas or macros, and an impressive benchmark.

  4. #4
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Is it possible to SUMPRODUCT over sectioned ranges? i.e
    =SUMPRODUCT(--(MOD(COLUMN(A2:Z2,E10:J30),3)=2),--(A2:Z2="Simon",E10:J30="Simon"),B2:AA2,F10:AA10)
    , i'm thinking if i had data in block ranges throughout my worksheet, of course the ranges could be named.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I have a technique for very ragged ranges, but in the situation that you include, I would think it simpler to do the two ranges as separate SPs, and add them.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Bob, for the benefit of others (well mine really ) could you take us step by step throught building a sumproduct formula, i.e what we must include and what we can include, and perhaps maybe the rules around what you intend to sum, e.g i notice in the formula you supplied you use the double urinary twice, i think it coerses the text to a value but not entirely sure of its function or when to use it.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Okay, but I need to add a bit of thinking on how to structure, so I will post tomorrow.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Thread moved to it's new home.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #9
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Thread closed in lieu of a series of tutorial threads dealing with the construction and understanding of the use of SUMPRODUCT by xld.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •