SUMPRODUCT and search string
 

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.


--
peacelittleone


------------------------------------------------------------------------
peacelittleone's Profile: http://www.excelforum.com/member.php...o&userid=20937
View this thread: http://www.excelforum.com/showthread...hreadid=379321

try this

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=379321

duane Wrote:
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.


--
peacelittleone


------------------------------------------------------------------------
peacelittleone's Profile: http://www.excelforum.com/member.php...o&userid=20937
View this thread: http://www.excelforum.com/showthread...hreadid=379321

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=379321

If you don't want it case sensitive, use

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

--
HTH

Bob Phillips

"duane" wrote in
message ...

try this

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


--
duane


------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=379321

Thank You!!!! :)


--
peacelittleone


------------------------------------------------------------------------
peacelittleone's Profile: http://www.excelforum.com/member.php...o&userid=20937
View this thread: http://www.excelforum.com/showthread...hreadid=379321