Help with formulas

[Kim]

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.

 

[Roger Govier]

Hi Kim

Create some Dynamic named ranges
Insert>Name>Define
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)*(Q3>ColQ))+1

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

--
Regards
Roger Govier

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

 

[Mike H]

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)*(Q3>INDIRECT("$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<>""))))=C3)*(INDIRECT("$F$3:$F" &
SUMPRODUCT(MAX((ROW(C1:C1000))*(C1:C1000<>""))))=F3)*(Q3>INDIRECT("$Q$3:$Q"
&SUMPRODUCT(MAX((ROW(C1:C1000))*(C1:C1000<>""))))))+1

As i said a bit long!!

Mike