| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT, LEN - simplify formula
=(SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CF2:CF64,LEN($A28))=$A28))+SUMPROD UCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CG2:CG64,LEN($A28))=$A28))
+ SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CH2:CH64,LEN($A28))=$A28))+ SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CI2:CI64,LEN($A28))=$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CJ2:CJ64,LEN($A28))=$A28))) There must be a simplier way of doing this??? I still need to add extra SUMPRODUCT's to the end of this formula, (a total of 8), but there must be a better way to shorten it? Any suggestions would be most welcome. Cheers |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, LEN - simplify formula
Try...
=SUM(IF(prospects!$C$2:$C$64=B$27,IF(LEFT(prospect s!CF2:CJ64,LEN($A28))=$ A28,1))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Fiona wrote: =(SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CF2:CF64,LEN($A28 ))=$A28))+SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CG2:CG64, LEN($A28))=$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CH2:CH64,LEN($A28)) =$A28))+ SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CI2:CI64,LEN($A28)) =$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CJ2:CJ64,LEN($A28)) =$A28))) There must be a simplier way of doing this??? I still need to add extra SUMPRODUCT's to the end of this formula, (a total of 8), but there must be a better way to shorten it? Any suggestions would be most welcome. Cheers |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, LEN - simplify formula
Why don't you add another column and do the selection there and then do the
countif. ie X1= Newspaper ad x2= =IF(ISERR(SEARCH($X$1,c2,1)),0,1) c1= =COUNTIF(c2:c64,1) not sure if it is what you are looking for. -- Hope this helps Martin Fishlock "Fiona" wrote: =(SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CF2:CF64,LEN($A28))=$A28))+SUMPROD UCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CG2:CG64,LEN($A28))=$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CH2:CH64,LEN($A28))=$A28))+ SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CI2:CI64,LEN($A28))=$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CJ2:CJ64,LEN($A28))=$A28))) There must be a simplier way of doing this??? I still need to add extra SUMPRODUCT's to the end of this formula, (a total of 8), but there must be a better way to shorten it? Any suggestions would be most welcome. Cheers |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, LEN - simplify formula
Domenic,
Thanks for that, but so far I haven't got it to work. I keep getting 0 as the result, which I know is not correct. {=SUM(IF(prospects!$CW$2:$CW$2000=I$54,IF(LEFT(pro spects!$CW$2:$DD$2000,LEN($A55))= $A55,1)))} What does "....confirmed with CONTROL+SHIFT+ENTER, not just ENTER" actually do differently? I know it puts the {} on each end of the formula, but what does that mean? "Domenic" wrote: Try... =SUM(IF(prospects!$C$2:$C$64=B$27,IF(LEFT(prospect s!CF2:CJ64,LEN($A28))=$ A28,1))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Fiona wrote: =(SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CF2:CF64,LEN($A28 ))=$A28))+SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CG2:CG64, LEN($A28))=$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CH2:CH64,LEN($A28)) =$A28))+ SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CI2:CI64,LEN($A28)) =$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CJ2:CJ64,LEN($A28)) =$A28))) There must be a simplier way of doing this??? I still need to add extra SUMPRODUCT's to the end of this formula, (a total of 8), but there must be a better way to shorten it? Any suggestions would be most welcome. Cheers |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, LEN - simplify formula
Just realised i'd pasted the wrong formula into my last post. But even after
fixing it, it still doesn't work :-( =SUM(IF(prospects!$C$2:$C$2000=G$54,IF(LEFT(prospe cts!$CW$2:$DD$2000,LEN($A55))= $A55,1))) Argghhh,.... I would be great if I could get this program quickly. Any help would be great. Cheers "Domenic" wrote: Try... =SUM(IF(prospects!$C$2:$C$64=B$27,IF(LEFT(prospect s!CF2:CJ64,LEN($A28))=$ A28,1))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Fiona wrote: =(SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CF2:CF64,LEN($A28 ))=$A28))+SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CG2:CG64, LEN($A28))=$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CH2:CH64,LEN($A28)) =$A28))+ SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CI2:CI64,LEN($A28)) =$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CJ2:CJ64,LEN($A28)) =$A28))) There must be a simplier way of doing this??? I still need to add extra SUMPRODUCT's to the end of this formula, (a total of 8), but there must be a better way to shorten it? Any suggestions would be most welcome. Cheers |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, LEN - simplify formula
I got it to work...Thank you very much.!! Not sure what I was doing wrong. =SUM(IF(prospects!$C$2:$C$2000=D$54,IF(LEFT(prospe cts!$CW$2:$DD$2000,LEN($A58))= $A58,1))) Could someone let me know what the CONTROL+SHIFT+ENTER, not just ENTER does?? Cheers "Domenic" wrote: Try... =SUM(IF(prospects!$C$2:$C$64=B$27,IF(LEFT(prospect s!CF2:CJ64,LEN($A28))=$ A28,1))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Fiona wrote: =(SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CF2:CF64,LEN($A28 ))=$A28))+SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CG2:CG64, LEN($A28))=$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CH2:CH64,LEN($A28)) =$A28))+ SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CI2:CI64,LEN($A28)) =$A28)) + SUMPRODUCT(--(prospects!$C$2:$C$64=B$27),--(LEFT(prospects!CJ2:CJ64,LEN($A28)) =$A28))) There must be a simplier way of doing this??? I still need to add extra SUMPRODUCT's to the end of this formula, (a total of 8), but there must be a better way to shorten it? Any suggestions would be most welcome. Cheers |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, LEN - simplify formula
Could someone let me know what the CONTROL+SHIFT+ENTER,
not just ENTER does?? It's a special way to confirm / register array formulas, such as the one posted by Domenic. Normal formulas would just require pressing ENTER, array formulas require pressing CONTROL+SHIFT+ENTER to "enter" the formula. The curly braces: { } are auto-inserted by Excel upon correct array-entering. We can use this as a visual check in the formula bar for any array-entered formulas. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula and multiplying conditions | Excel Worksheet Functions | |||
| Is it possible? | Excel Worksheet Functions | |||
| Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
