sumproduct or countif
 

Is there a way to use the sumproduct function to get the same result as the
following: countif (range, "*text*")

I'm using the sumproduct to do a count based on 2 criteria being true in 2
separate columns, but I can't guarantee that the text I';m looking for (BDS)
will always be at the beginning of the text string of the second column. It
may be in the middle, at the end, or the beginning.

Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks.

sumproduct or countif
 

maybe:

=SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse"))



mpierre wrote:

Is there a way to use the sumproduct function to get the same result as the
following: countif (range, "*text*")

I'm using the sumproduct to do a count based on 2 criteria being true in 2
separate columns, but I can't guarantee that the text I';m looking for (BDS)
will always be at the beginning of the text string of the second column. It
may be in the middle, at the end, or the beginning.

Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks.

--

Dave Peterson

sumproduct or countif
 

If you want it case sensitive, change the SEARCH to FIND

=SUMPRODUCT(--(--ISNUMBER(FIND("bds",$A$1:$A$10))),--(B1:B10="somethingelse"
))

--

HTH

RP

"Dave Peterson" wrote in message
...
maybe:

=SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse"))



mpierre wrote:

Is there a way to use the sumproduct function to get the same result as
the
following: countif (range, "*text*")

I'm using the sumproduct to do a count based on 2 criteria being true in
2
separate columns, but I can't guarantee that the text I';m looking for
(BDS)
will always be at the beginning of the text string of the second column.
It
may be in the middle, at the end, or the beginning.

Would really appreciate any help anyone (Frank , maybe?) could offer.
Thanks.

--

Dave Peterson

sumproduct or countif
 

And maybe change the lower case bds to BDS (to match the original post)--or even
(BDS).



Bob Phillips wrote:

If you want it case sensitive, change the SEARCH to FIND

=SUMPRODUCT(--(--ISNUMBER(FIND("bds",$A$1:$A$10))),--(B1:B10="somethingelse"
))

--

HTH

RP

"Dave Peterson" wrote in message
...
maybe:

=SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse"))



mpierre wrote:

Is there a way to use the sumproduct function to get the same result as
the
following: countif (range, "*text*")

I'm using the sumproduct to do a count based on 2 criteria being true in
2
separate columns, but I can't guarantee that the text I';m looking for
(BDS)
will always be at the beginning of the text string of the second column.
It
may be in the middle, at the end, or the beginning.

Would really appreciate any help anyone (Frank , maybe?) could offer.
Thanks.

--

Dave Peterson


--

Dave Peterson

sumproduct or countif
 

This worked great. THANK you very much.

"Dave Peterson" wrote:

maybe:

=SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse"))



mpierre wrote:

Is there a way to use the sumproduct function to get the same result as the
following: countif (range, "*text*")

I'm using the sumproduct to do a count based on 2 criteria being true in 2
separate columns, but I can't guarantee that the text I';m looking for (BDS)
will always be at the beginning of the text string of the second column. It
may be in the middle, at the end, or the beginning.

Would really appreciate any help anyone (Frank , maybe?) could offer. Thanks.

--

Dave Peterson