sumproduct or countif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
maybe:

=SUMPRODUCT(--(ISNUMBER(SEARCH("bds",A1:A10))),--(B1:B10="somethingelse"))
 
If you want it case sensitive, change the SEARCH to FIND

=SUMPRODUCT(--(--ISNUMBER(FIND("bds",$A$1:$A$10))),--(B1:B10="somethingelse"
))
 
And maybe change the lower case bds to BDS (to match the original post)--or even
(BDS).
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Need Countifs Formula Help 0
SUMPRODUCT Error 3
modifying countifs 2
Conditionally count unique values 2
Using SUMPRODUCT where one of the columns can be either TEST or aNUMBER 1
countif using multiple criteria 2
Insert columns and values programmatically with IF statement 9
Excel Sumproduct 0

Back
Top