SUMPRODUCT and search string

**peacelittleone** · 06-15-2005, 09:44 AM

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.

**duane** · 06-15-2005, 09:59 AM

try this

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

**peacelittleone** · 06-15-2005, 10:14 AM

Originally Posted by duane

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** · 06-15-2005, 10:21 AM

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

**peacelittleone** · 06-15-2005, 10:24 AM

Thank You!!!!

Bob Phillips · 06-15-2005, 11:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-05-2005, 10:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-05-2005, 11:05 PM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-06-2005, 12:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-06-2005, 01:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-06-2005, 02:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-06-2005, 03:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-06-2005, 04:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-06-2005, 06:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Bob Phillips · 09-06-2005, 07:05 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

SUMPRODUCT and search string

LinkBack

Thread Tools

Rate This Thread

Display

SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Re: SUMPRODUCT and search string

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions