Help with formulas
 

I have this formulas
=SUMPRODUCT(($C$3:$C$340=C3)*($F$3:$F$340=F3)*(Q3 $Q$3:$Q$340))+1

Is there a way where I can replace $C$340 with something that it will look
at the last row. I know it can be done with a macro $C$" & lngRow & " but I
want to avoid that.

Thanks.

Help with formulas
 

Hi Kim

Create some Dynamic named ranges
InsertNameDefine
Name ColC ( or Sales or whatever it contains)
Refers to =$C$3:INDEX($C:$C,COUNTA($C:$C))

Repeat process for other columns then use
=SUMPRODUCT((ColC=C3)*(ColF=F3)*(Q3ColQ))+1

For more information on creating named ranges take a look at
http://www.contextures.com/xlNames03.html

--
Regards
Roger Govier

"Kim" wrote in message
...
I have this formulas
=SUMPRODUCT(($C$3:$C$340=C3)*($F$3:$F$340=F3)*(Q3 $Q$3:$Q$340))+1

Is there a way where I can replace $C$340 with something that it will look
at the last row. I know it can be done with a macro $C$" & lngRow & " but
I
want to avoid that.

Thanks.

__________ Information from ESET Smart Security, version of virus
signature database 4531 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4532 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Help with formulas
 

Ki,

Try this. You can get it all in one formula but it gets a bit long so I
split it into 2.

Put this in (Say) A1 which returns the last used row in the range C1:C1000
=SUMPRODUCT(MAX((ROW(C1:C1000))*(C1:C1000<"")))

Now modify your formula like this
=SUMPRODUCT((INDIRECT("$C$3:$C" & A1)=C3)*(INDIRECT("$F$3:$F" &
A1)=F3)*(Q3INDIRECT("$Q$3:$Q" &A1)))+1

If you want it all in one formula.

=SUMPRODUCT((INDIRECT("$C$3:$C" &
SUMPRODUCT(MAX((ROW(C1:C1000))*(C1:C1000<""))))=C 3)*(INDIRECT("$F$3:$F" &
SUMPRODUCT(MAX((ROW(C1:C1000))*(C1:C1000<""))))=F 3)*(Q3INDIRECT("$Q$3:$Q"
&SUMPRODUCT(MAX((ROW(C1:C1000))*(C1:C1000<""))))) )+1

As i said a bit long!!

Mike

"Kim" wrote:

I have this formulas
=SUMPRODUCT(($C$3:$C$340=C3)*($F$3:$F$340=F3)*(Q3 $Q$3:$Q$340))+1

Is there a way where I can replace $C$340 with something that it will look
at the last row. I know it can be done with a macro $C$" & lngRow & " but I
want to avoid that.

Thanks.