Using OFFSET and/or COUNTA within SUMPRODUCT Function

**maacmaac** · 10-20-2012, 11:28 PM

Is it possible to use an OFFSET and/or COUNTA formula within a SUMPRODUCT formula? I am using a SUMPRODUCT formula, which is using rows 13-33. I want the array to always start in row 13 but the last row is always changing (i.e. today the last row of data is row 33 but next week the last row might be row 1000. Can I build something into the formula to account for this? I've attached a sample of how the data is layed out (current formula is in D2:D6).

Please Login or Register  to view this content.

**icestationzbra** · 10-20-2012, 11:58 PM

explore the feature of Excel Table - they offer auto-extension of ranges when used within formulae.

**Nels** · 10-21-2012, 12:44 AM

I've looked into the excel and made some change to the formula.

In the formula, I used column A as reference, that means column A value cannot be blank until last row.
Hope this suit to your need.

The current fomula in D2 is now as below:

Formula:

Please Login or Register  to view this content.

Here is the edited excel file:
Excel Tip - Offset within Sumproduct Formula.xls

**maacmaac** · 10-21-2012, 12:48 AM

In reference to the "Excel Table" feature solution, expect for one issue, this works perfect. The only comment I have, and not sure how to address/fix, is that whenever I add a new row of information, the font with formulas automatically changes to "MS Sans Serif". Is there a way to avoid this? Thanks again for comments.

**maacmaac** · 10-21-2012, 01:08 AM

Sorry the issue was for the solution to use the "Excel Table" feature....once a start a new row of data the cells with the formulas changes to MS Sans Serif font from Arial. Not sure why this is happening. Thanks in advance for any comments.

**Nels** · 10-21-2012, 01:17 AM

I cannot reproduce the issue, don't know why the issue happens.

Would you mind attaching the excel file which the issue happens(or that's the file I just uploaded)?
Also if you can provide a reproduce step that can be helpful to troubleshoot the issue. Thanks

**maacmaac** · 10-21-2012, 01:58 AM

Attached is copy of sample. When I start entering new line of data, the font style changes. No big deal, I can work around it but just curious as to why the style would change.

**Nels** · 10-21-2012, 03:51 AM

I canceled the form auto expend function then the font does not change anymore
, the issue can because of the auto expend function setting.
(However, you will find that the form style such as lines will not auto assign in new filled row, this is the con of the solution)

Hope this helps.