Hi everyone, quick question:

How can I use IF(ISNUMBER(SEARCH... within a SUMPRODUCT statement?

Here's the code I came up with, but it doesn't really seem to work:

=SUMPRODUCT((Strategies!$B$3:$B$62=1)*(Strategies! $E$3:$E$62=(IF(ISNUMBER(SEARCH(1,Strategies!$E$3:$ E$62)),1,0))))/(COUNTIF(Strategies!$B$2:$B$62,1))

Any suggestions?

IF should not be involved

=SUMPRODUCT((Strategies!$B$3:$B$62=1)*(ISNUMBER(SE ARCH(1,Strategies!$E$3:$E$
62))))

will count the values, if you need to sum you need to add an array but since
I don't know what you are summing you have to post back
I also noted that your countif uses B2:B62 not B3:B62


--

Regards,

Peo Sjoblom


replace
"DJ_Swammi" wrote in message
...
Hi everyone, quick question:

How can I use IF(ISNUMBER(SEARCH... within a SUMPRODUCT statement?

Here's the code I came up with, but it doesn't really seem to work:


=SUMPRODUCT((Strategies!$B$3:$B$62=1)*(Strategies! $E$3:$E$62=(IF(ISNUMBER(SE
ARCH(1,Strategies!$E$3:$E$62)),1,0))))/(COUNTIF(Strategies!$B$2:$B$62,1))

Any suggestions?

Hi!

Try this:

=SUMPRODUCT(--(Strategies!$B$3:$B$62=1),--(ISNUMBER(FIND(1,Strategies!$E$3:$E$62))))/COUNTIF(Strategies!$B$2:$B$62,1)

NB: If COUNTIF(Strategies!$B$2:$B$62,1) = 0 you'll get a #DIV/0! error.

Biff

"DJ_Swammi" wrote in message
...
Hi everyone, quick question:

How can I use IF(ISNUMBER(SEARCH... within a SUMPRODUCT statement?

Here's the code I came up with, but it doesn't really seem to work:

=SUMPRODUCT((Strategies!$B$3:$B$62=1)*(Strategies! $E$3:$E$62=(IF(ISNUMBER(SEARCH(1,Strategies!$E$3:$ E$62)),1,0))))/(COUNTIF(Strategies!$B$2:$B$62,1))

Any suggestions?

Thanks Biff, that seemed to do the trick!

"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(Strategies!$B$3:$B$62=1),--(ISNUMBER(FIND(1,Strategies!$E$3:$E$62))))/COUNTIF(Strategies!$B$2:$B$62,1)

NB: If COUNTIF(Strategies!$B$2:$B$62,1) = 0 you'll get a #DIV/0! error.

Biff

"DJ_Swammi" wrote in message
...
Hi everyone, quick question:

How can I use IF(ISNUMBER(SEARCH... within a SUMPRODUCT statement?

Here's the code I came up with, but it doesn't really seem to work:

=SUMPRODUCT((Strategies!$B$3:$B$62=1)*(Strategies! $E$3:$E$62=(IF(ISNUMBER(SEARCH(1,Strategies!$E$3:$ E$62)),1,0))))/(COUNTIF(Strategies!$B$2:$B$62,1))

Any suggestions?