SUMPRODUCT, LEN - simplify formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fiona - 24 Nov 2006 03:17 GMT

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

Reply to this Message

Domenic - 24 Nov 2006 03:54 GMT

Try...

=SUM(IF(prospects!$C$2:$C$64=B$27,IF(LEFT(prospects!CF2:CJ64,LEN($A28))=$
A28,1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

> =(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,
[quoted text clipped - 15 lines]
>
> Cheers

Reply to this Message

Fiona - 24 Nov 2006 04:21 GMT

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(prospects!$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?

> Try...
>
[quoted text clipped - 24 lines]
> >
> > Cheers

Reply to this Message

Fiona - 24 Nov 2006 04:53 GMT

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(prospects!$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

> Try...
>
[quoted text clipped - 24 lines]
> >
> > Cheers

Reply to this Message

Fiona - 24 Nov 2006 05:12 GMT

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(prospects!$CW$2:$DD$2000,LEN($A58))=
$A58,1)))

Could someone let me know what the CONTROL+SHIFT+ENTER, not just ENTER does??

Cheers

> Try...
>
[quoted text clipped - 24 lines]
> >
> > Cheers

Reply to this Message

Max - 24 Nov 2006 07:11 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Martin Fishlock - 24 Nov 2006 04:14 GMT

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.

Signature

Hope this helps
Martin Fishlock

> =(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))
> +
[quoted text clipped - 10 lines]
>
> Cheers

Reply to this Message