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

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

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

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

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

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

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
---