SUMPRODUCT and search string

P

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*")*(D1:D700=""))

Any help is appriciated.

Thank you,

Heather.
 
P

peacelittleone

duane said:
try this

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


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

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
 
B

Bob Phillips

If you don't want it case sensitive, use

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