Need help with keyword search function using SUMPRODUCT

[dellehurley]

Hi
I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned.
I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search.
=SUMPRODUCT(--('Books List'!G2:G1166=D8))

Can anyone help?
Thanks
delle

 

[barry houdini]

You can try like this:

=COUNTIF('Books List'!G2:G1166,"*"&D8&"*")

 

[jbeaucaire]

How about:

=COUNTIF('Books List'!G:G, "*" & D8 & "*")

Or if you don't want to refer to the whole column, you can set the range more specifically.

(EDIT: Hat-tip to Barry...)

 

[dellehurley]

You can try like this:

=COUNTIF('Books List'!G2:G1166,"*"&D8&"*")

Thanks for the reply.

That worked however now the sum that is using this count results in NUM!
THe other equation is
=IF(ROWS(A$14:A14)<=$D$9,INDEX('Books List'!C$2:C$1166,SMALL(IF('Books List'!$G$2:$G$1166=$D$8,ROW('Books List'!$G$2:$G$1166)-ROW('Books List'!$G$2)+1),ROWS(A$14:A14))),"")

Any ideas?
delle

 

[jbeaucaire]

Don't you need to make that same change to the D8 reference? In the big formula, $D$8 needs to change to "*"&$D$8&"*", I'm pretty sure.

 

[barry houdini]

So D9 contains the COUNTIF formula?

Try changing to this

=IF(ROWS(A$14:A14)<=$D$9,INDEX('Books List'!C$2:C$1166,SMALL(IF(ISNUMBER(SEARCH($D$8,'Books List'!$G$2:$G$1166)),ROW('Books List'!$G$2:$G$1166)-ROW('Books List'!$G$2)+1),ROWS(A$14:A14))),"")

 

[dellehurley]

So D9 contains the COUNTIF formula?

Try changing to this

=IF(ROWS(A$14:A14)<=$D$9,INDEX('Books List'!C$2:C$1166,SMALL(IF(ISNUMBER(SEARCH($D$8,'Books List'!$G$2:$G$1166)),ROW('Books List'!$G$2:$G$1166)-ROW('Books List'!$G$2)+1),ROWS(A$14:A14))),"")

Sorry I should have said that D8 contains the search word and D9 contains the formula you originally helped me with.
I tried the change and it comes up with a totally unrelated book in the first line and then #NUM! on the following lines. I placed dog in D8 and D9= 19. As you can see a book named "Koala" showed up which obviously does not contain "dog"

<TABLE style="WIDTH: 265pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=353 x:str><COLGROUP><COL style="WIDTH: 197pt; mso-width-source: userset; mso-width-alt: 9618" width=263><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #339966; WIDTH: 197pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2105718 class=xl25 height=20 width=263>Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #339966; WIDTH: 68pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=90>Author</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #969696 0.5pt solid; BORDER-RIGHT: #969696 0.5pt solid" class=xl24 height=17>Koala</TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696 0.5pt solid; BORDER-RIGHT: #969696 0.5pt solid" class=xl24>Steve Parish</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl24 height=17 x:err="#NUM!">#NUM!</TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl24 x:err="#NUM!">#NUM!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl24 height=17 x:err="#NUM!">#NUM!</TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696; BACKGROUND-COLOR: transparent; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl24 x:err="#NUM!">#NUM!</TD></TR></TBODY></TABLE>

Any ideas what I am doing wrong??
delle

 

[dellehurley]

I forgot to say I also tried with "*"&D8&"*" and the result is the same
delle

 

[barry houdini]

The long formula is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER.

Select the cell with the first instance of that formula, press F2 key then hold down CTRL and SHIFT keys while pressing ENTER. Curly braces like { and } should appear around the formula in the formula bar.....now you can copy the formula down the column in the usual way

 

[dellehurley]

I'm still having issues however I have to sign off now as I'm at work at the head honcho needs me.
Thanks for your help.
I'll post again later.

delle