Search from cell

[spiderkzn]

Hi,

I've trying to develop the search function in formula which it bring innocent result or zero. So I hope you will able to help. So one example of line of code which I've used in SUMPRODUCT, for example.

(ISNUMBER(SEARCH({"Rottweiller", "Beagle", "Bulldog", "Greyhound", "Poodle"},'Dog Data'!C:C))

So The line above, it work with return number. But I've developing something else which Just in case I might need add dog breed which I do not have to modify the code. So the table below that I'm looking for

Dog Breed

Rottweiller
Beagle
Bulldog
Greyhound
Poodle

I've highlight eg F3:F7 which I've use DogBreed in Name Manager in formula.

So I've add DogBreed in line of code below


(ISNUMBER(SEARCH(DogBreed,'Dog Data'!C:C))

But the problem is it return zero? Have I enter code wrong?

Please may I ask you with help of example of code

Many Thank

 

[StephenCrump]

Try: COUNTIF('Dog Data'!C1:C1000,"*" & DogBreed & "*")

which you can then also wrap in a SUMPRODUCT().

 

[Aladin Akyurek]

It's clear what the intent is...

If you need partial matching and a total count...

=SUMPRODUCT(ISNUMBER(SEARCH(DogBreed,'Dog Data'!$C$2:$C$1000))+0)

If you need just whole item matching and a total count...

=SUMPRODUCT(ISNUMBER(MATCH('Dog Data'!$C$2:$C$1000,DogBreed,0))+0)

The latter is a tad faster than a set up with COUNTIF(S).

 

[spiderkzn]

Hi there,

I've tried the code above as it still invalid like N/A or zero. here my full code for example I need look for number of dog breed as below in bold which set in New York and date range which is working fine.

=SUMPRODUCT(
--('Dog Data'!A:A='New York')*
(ISNUMBER(SEARCH({"Rottweiller", "Beagle", "Bulldog", "Greyhound", "Poodle"},'Dog Data'!E:E)))*
(--('RAW Data'!B:B>=DATE(2017,1,1)))*
(--('RAW Data'!F:F<=DATE(2017,6,30))))

So what I'm looking for the simple search like code below but it return zero, which I dont have to add or remove as the search which I do not have to modify the formula which it will save lot of time.



=SUMPRODUCT(
--('Dog Data'!A:A='New York')*
(ISNUMBER(SEARCH(DogBreed,'Dog Data'!E:E)))*
(--('RAW Data'!B:B>=DATE(2017,1,1)))*
(--('RAW Data'!F:F<=DATE(2017,6,30))))

Maybe if you can modify the formula and show me which if it work.

Many Thanks

D

****** id="cke_pastebin" style="position: absolute; top: 152px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">=SUMPRODUCT(
--('Dog Data'!A:A='New York')*
(ISNUMBER(SEARCH({"Rottweiller", "Beagle", "Bulldog", "Greyhound", "Poodle"},'Dog Data'!E:E)))*
(--('RAW Data'!B:B>=DATE(2017,1,1)))*
(--('RAW Data'!F:F<=DATE(2017,6,30))))
</body>

 

[Aladin Akyurek]

Is it Dog Data or RAW Data or both?

 

[spiderkzn]

Aladdin, Thank you for spotting it. I tried look for edit. So I've make error. the below is correct as Only focus on Dog Data. not RAW Data

=SUMPRODUCT(
--('Dog Data'!A:A='New York')*
(ISNUMBER(SEARCH(DogBreed,'Dog Data'!E:E)))*
(--('Dog Data'!B:B>=DATE(2017,1,1)))*
(--('Dog Data'!F:F<=DATE(2017,6,30))))

 

[Aladin Akyurek]

Aladdin, Thank you for spotting it. I tried look for edit. So I've make error. the below is correct as Only focus on Dog Data. not RAW Data

=SUMPRODUCT(
--('Dog Data'!A:A='New York')*
(ISNUMBER(SEARCH(DogBreed,'Dog Data'!E:E)))*
(--('Dog Data'!B:B>=DATE(2017,1,1)))*
(--('Dog Data'!F:F<=DATE(2017,6,30))))

Try...

=SUMPRODUCT(
     --('Dog Data'!$A$2:$A$1000="New York"),
     --ISNUMBER(MATCH('Dog Data'!$E$2:$E$1000,Dogbreed,0)),
     --('Dog Data'!$B$2:$B$1000>=DATE(2017,1,1)),
     --('Dog Data'!$F$2:$F$1000<=DATE(2017,6,30)))

 

[spiderkzn]

try...

=sumproduct(
     --('dog data'!$a$2:$a$1000="new york"),
     --isnumber(match('dog data'!$e$2:$e$1000,dogbreed,0)),
     --('dog data'!$b$2:$b$1000>=date(2017,1,1)),
     --('dog data'!$f$2:$f$1000<=date(2017,6,30)))

omg!!!! Thank you thank you thank you!!!! It working!!!!

 

[Aladin Akyurek]

omg!!!! Thank you thank you thank you!!!! It working!!!!

You are welcome.