Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |