# Thread: sumproduct with variable ranges (Excel 2003)

1. ## 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. ## 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. ## 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
•