Sumproduct, dynamic range

**jmicdk** · 04-18-2008, 06:06 PM

This was originally posted under Worksheet functions forum, i deleted it there and moved it to this forum. No cross-forum posting occured. Thanks

This is part of a routine.

I want to do an average for a certain range. Application.Average will not work in this case.
I have a named range (f) in col c that looks up a value in the range and do some calulations based on conditions on subseqeunt cols.
The sumif part works fine but when i use columns() in sumproduct() it results in an error.

Any help appreciated

=SUMIF(f,"X",OFFSET(f,0,(COLUMN()-3)))/SUMPRODUCT(N(f="X"),N((OFFSET(f,0,(COLUMN()-3))>0)))

Attached is an example of how how the formula should work. but i can't get it to do so.

Thanks

**daddylonglegs** · 04-18-2008, 07:53 PM

I'm not quite sure why COLUMN() doesn't work but perhaps try using COLUMNS function like this

=SUMIF(f,"x",OFFSET(f,0,COLUMNS($E37:E37)))/SUMPRODUCT(N(f="x"),N(OFFSET(f,0,COLUMNS($E37:E37))>0))

in E37 copied across

It's more robust too because it still gives the correct result if columns are added or deleted