sumproduct

[mpierre]

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

I'm trying to use sumproduct to count the number of occurances where the
condition in column A is true and the condition in column B is also true, but
I am searching for text in column B (the word "other"). The text may appear
in the beginning, middle, or end of a string of text in the second column.

I would really appreciate any suggestions you could offer. Thanks.

 

[Myrna Larson]

The following worked for me to count all cells containing "a"

=SUMPRODUCT(--ISNUMBER(FIND("a",A1:A20)))

 

[Biff]

Hi!

Here's one way:

=SUMPRODUCT(--(A1:A3=whatever),--(ISNUMBER(SEARCH(" other "," "&B1:B3&"
"))))

Biff

 

[Harlan Grove]

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

I'm trying to use sumproduct to count the number of occurances where the
condition in column A is true and the condition in column B is also true,
but I am searching for text in column B (the word "other"). The text may
appear in the beginning, middle, or end of a string of text in the second
column.

SUMPRODUCT isn't be the only approach for this. You could use

=SUMPRODUCT(--(BooleanExpressionInvolvingColA),
--ISNUMBER(FIND("other",ColB)))

or you try the array formula

=COUNT(1/(SEARCH("TRUE*other*",(BooleanExpressionInvolvingColA)&ColB)=1))

 

[mpierre]

Excellent solution. It worked great. Thank you.