Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumproduct with variable ranges (Excel 2003)

    I'm creating a summary worksheet which totals (counts) several fields from other worksheets. The other worksheets use many sumproduct formulas to get the data since I use drop down lists (via data vaildation) to let the user select certain options. I'm stuck on how to do the same thing for a summary (of the 5 individual sheets) where the # of rows is not the same for each sheet. I use indirect for the worksheet name but can't figure out how to capture the # of rows which can be different for each sheet.

    A sample formula for one of the totals is =SUMPRODUCT(($I$15:$I$38=$C9)*(($G$15:$G$38=6)+($G $15:$G$38=7)))

    This formula gives me a single total # for each entry in col G that equals 6 or 7, and rows in col I that match data in C9. In the other worksheets, the same formula is used, however the end row is not the same (start row is always 15).

    How can I programmatically determine the end row (G38 in this example)? I was thinking of using offset

    Thmx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sumproduct with variable ranges (Excel 2003)

    If there are no data below the ranges you mention, you can use dynamic ranges definde in Insert | Name | Define, for example a range named IValues that refers to
    <code>
    =OFFSET('SheetName'!$I$15,0,0,COUNTA('SheetName'!$ G$15:$G$65536),1)
    </code>
    and similarly a range named GValues that refers to
    <code>
    =OFFSET('SheetName'!$G$15,0,0,COUNTA('SheetName'!$ G$15:$G$65536),1)
    </code>
    Replace SheetName with the actual name of your worksheet.
    I used COUNTA on column G in both cases to ensure that both ranges have the same size.
    You can then use
    <code>
    =SUMPRODUCT(IValues=$C9)*((GValues=6)+(GValues=7)) )</code>

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct with variable ranges (Excel 2003)

    Ahh yes, that will work just fine <img src=/S/clever.gif border=0 alt=clever width=15 height=15> I've used these dynamic ranges (with 'offset') before but just wasn't sure how to incorporate it into my sumproduct. To reply to your warning - there is no data after the rows for the indivdual data sheets so I can use COUNTA. I made sure of that when I created these templates for the users. I added a colored row at the end of the data set with a note to the users to only insert new rows above this line. This lets the formulas auto-expand to include these new rows which keeps the summaries updated for any added data.

    Thanks. Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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