I have a dynamic range named code_db. It is a single column which expands as
new codes are added. I want to sum a column which is offest 22 columns. For
Excel 2007 the following formula works fine:
=(SUMIFS(OFFSET(Code_db,0,22),Code_db,$U$3&M7,OFFS ET(Code_db,0,9),"Water"))

I'm trying to make it compatible with Excel 2003 using sumproduct and
offsets, but keep getting "#Value" when I enter the formula:
=SUMPRODUCT(--(OFFSET(Code_db,0,22)),--(Code_db=$U$3&M7),--(OFFSET(Code_db,0,9)="Water"))

Is it possible to use sumproducts and offsets?

Thanks for your help!

Mike

Hi,

I can't see what's in each of your ranges but the formula does not return an
error on my machine. One question the range Code_db - what does that
represent?

In 2003 you can't refer to an entire column in a sumproduct formula, but you
can in 2007. So if Code_db represents something like A:A it won't work.

If this helps, please click the Yes button.

Shane Devenshire

"mike in texas" wrote:

I have a dynamic range named code_db. It is a single column which expands as
new codes are added. I want to sum a column which is offest 22 columns. For
Excel 2007 the following formula works fine:
=(SUMIFS(OFFSET(Code_db,0,22),Code_db,$U$3&M7,OFFS ET(Code_db,0,9),"Water"))

I'm trying to make it compatible with Excel 2003 using sumproduct and
offsets, but keep getting "#Value" when I enter the formula:
=SUMPRODUCT(--(OFFSET(Code_db,0,22)),--(Code_db=$U$3&M7),--(OFFSET(Code_db,0,9)="Water"))

Is it possible to use sumproducts and offsets?

Thanks for your help!

Mike

Shane thanks for the quick response.

"code_db" is a range d5:d? depending on the number of entires in D.

I discovered that I needed to offset (1,22) rather than (0,22) to prevent
including the text at the top of the column. this formula works fine:

=SUMPRODUCT(--(OFFSET(Code_db,1,22)),--(OFFSET(Code_db,1,0)=$U$3&M7),--(OFFSET(Code_db,1,9)="Water"))

Apparently sumproduct doesn't like to sum up text.

Thanks

Mike


"Shane Devenshire" wrote:

Hi,

I can't see what's in each of your ranges but the formula does not return an
error on my machine. One question the range Code_db - what does that
represent?

In 2003 you can't refer to an entire column in a sumproduct formula, but you
can in 2007. So if Code_db represents something like A:A it won't work.

If this helps, please click the Yes button.

Shane Devenshire

"mike in texas" wrote:

I have a dynamic range named code_db. It is a single column which expands as
new codes are added. I want to sum a column which is offest 22 columns. For
Excel 2007 the following formula works fine:
=(SUMIFS(OFFSET(Code_db,0,22),Code_db,$U$3&M7,OFFS ET(Code_db,0,9),"Water"))

I'm trying to make it compatible with Excel 2003 using sumproduct and
offsets, but keep getting "#Value" when I enter the formula:
=SUMPRODUCT(--(OFFSET(Code_db,0,22)),--(Code_db=$U$3&M7),--(OFFSET(Code_db,0,9)="Water"))

Is it possible to use sumproducts and offsets?

Thanks for your help!

Mike