SUMPRODUCT and search string

[peacelittleone]

In column B I have text that may or may not contain the word "foo".
In column D I have cells that may or may not contain text.

I want to be able to count the number of rows where BX contains "foo"
and column D is not empty (or is empty - I can take either).

What I have is:

=SUMPRODUCT((B1:B700="*foo*")*(D1700=""))

Any help is appriciated.

Thank you,

Heather.

 

[duane]

try this

=SUMPRODUCT((NOT(ISERROR(FIND("foo",$B$1:$B$700))))*($D$1:$D$700="")

 

[peacelittleone]

try this

=SUMPRODUCT((NOT(ISERROR(FIND("foo",$B$1:$B$700))))*($D$1:$D$700=""))

Tried. Still getting 0 as an answer.

Here is some sample data...

B D
1 Heading
2 This is some foo text
3 This is some foo text This is a non blank cell
4 This is some foo text
5 Heading
6 This is some foo text
7 This is some foo text

Here is my formula:

=SUMPRODUCT((NOT(ISERROR(FIND("foo",$B$1:$B$8)))),($D$1:$D$8=""))

I am getting 0 when I should be getting 3.

ugh.

 

[duane]

you pasted this

=SUMPRODUCT((NOT(ISERROR(FIND("foo",$B$1:$B$8)))),($D$1:$D$8=""))


which should be

=SUMPRODUCT((NOT(ISERROR(FIND("foo",$B$1:$B$700))))*($D$1:$D$700=""))

note the * in the middle instead of your comma

 

[Bob Phillips]

If you don't want it case sensitive, use

=SUMPRODUCT(--(NOT(ISERROR(SEARCH("foo",$B$1:$B$700)))),--($D$1:$D$700=""))

 

[peacelittleone]

Thank You!!!! :