Fix columns for sumproduct

Discussion in 'Microsoft Excel Discussion' started by Dougal Ross, Mar 15, 2012.

  1. Dougal Ross

    Dougal Ross Guest

    Hi,
    I have 2 formulas I am using to look for conditions in my data.
    One to find when one of two criteria is met:
    =SUMPRODUCT((V4:V20>1.05)+(X4:X20>8.05))

    and one to find when BOTH criteria are met:
    =SUMPRODUCT(--(V4:V20>1.05),--(X4:X20>8.05))

    I am regularly adding to the data and I only want these formulas to
    always work for the last 16 rows only (rows 4-20). At the moment
    however if I insert a row the formulas change from row 20 to 21. How
    do I fix it at 20 so when I insert a new row it doesn't change?

    Thanks
     
    Dougal Ross, Mar 15, 2012
    #1
    1. Advertisements

  2. Dougal Ross

    Dougal Ross Guest

    Sorry, the headng should say "Fix cells" not columns.
     
    Dougal Ross, Mar 15, 2012
    #2
    1. Advertisements

  3. Dougal Ross

    joeu2004 Guest

    "Dougal Ross" <> wrote:
    > I have 2 formulas I am using to look for conditions in my data.
    > One to find when one of two criteria is met:
    > =SUMPRODUCT((V4:V20>1.05)+(X4:X20>8.05))
    >
    > and one to find when BOTH criteria are met:
    > =SUMPRODUCT(--(V4:V20>1.05),--(X4:X20>8.05))
    >
    > I am regularly adding to the data and I only want these formulas to
    > always work for the last 16 rows only (rows 4-20). At the moment
    > however if I insert a row the formulas change from row 20 to 21.
    > How do I fix it at 20 so when I insert a new row it doesn't change?


    I'm confused. If you "want these formulas to always work for the last 16
    rows only", don't you want V4:V20 and X4:X20 to change to V5:V21 and X5:X21
    when you add one new row, for example, instead of "fix[ing] it at 20"?

    (If you want to "fix it at 20", you might use INDIRECT("V4:V20") for
    example. Otherwise....)

    Assuming you want V5:V21 and X5:X21, and assuming that column V and X always
    have the same number of lines of data, I would suggest that you put the
    following formula into some cell, e.g. Z1:

    =MATCH(1E300,V:V)

    If you know that the data will never go beyond row 10000, say, it would be
    better to write:

    =MATCH(1E300,V1:V10000)

    The MATCH formulas produce the row number of the last number in column V
    (and X presumably). 1E300 is presumably larger than any expected number in
    volumn V.

    Then, your SUMPRODUCT formulas can be (with some other unrelated corrections
    and improvements):

    =SUMPRODUCT(--((INDEX(V:V,Z1-19):INDEX(V:V,Z1)>1.05)
    +(INDEX(X:X,Z1-19):INDEX(X:X,Z1)>8.05)>0))

    =SUMPRODUCT((INDEX(V:V,Z1-19):INDEX(V:V,Z1)>1.05)
    *(INDEX(X:X,Z1-19):INDEX(X:X,Z1)>8.05))

    Note the important correction in the first formula. Your intention is to
    count the number of rows when V>1.05 or X>8.05. If both conditions are
    true, your original formula would count as 2. I presume you want to count
    as 1. Right?

    Also note the simplication(?) in the second formula. It is certainly easier
    to write. It might also be more efficient.

    Finally, if you are using XL2007 or later, you can replace the last
    SUMPRODUCT with the following:

    =COUNTIFS(INDEX(V:V,Z1-19):INDEX(V:V,Z1),">1.05",
    INDEX(X:X,Z1-19):INDEX(X:X,Z1),">8.05")

    FYI, I use the form INDEX:INDEX instead of OFFSET(V:V,Z1-19,0,20,1), for
    example, because OFFSET is a volatile function, whereas INDEX is not.
    Formulas that use OFFSET are recalculated every time any cell in any
    worksheet in the workbook is edited. Not so bad if you have only a few such
    formulas. It can slow thing down significantly if you have thousands of
    such formulas.
     
    joeu2004, Mar 15, 2012
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    2
    Views:
    256
    JE McGimpsey
    Apr 4, 2006
  2. Replies:
    7
    Views:
    528
    Gord Dibben
    Mar 1, 2007
  3. cpliu

    average multiple columns but skip a few columns

    cpliu, Dec 5, 2008, in forum: Microsoft Excel Discussion
    Replies:
    6
    Views:
    311
    cpliu
    Jan 7, 2009
  4. ram

    SUM 2 COLumns values with common names in another columns

    ram, Jan 21, 2009, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    187
    Pete_UK
    Jan 23, 2009
  5. cpliu
    Replies:
    1
    Views:
    413
    cpliu
    May 24, 2012
Loading...

Share This Page