View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Roger Govier[_3_] Roger Govier[_3_] is offline
external usenet poster
 
Posts: 2,480
Default 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