You changed Bob's code.
=SUMPRODUCT(--(ISNUMBER(SEARCH("clean",A1:A5))),--(B1:B5=1))
worked fine. Notice the additional double minus signs and parentheses.
The -- changes booleans (true/falses) to 1's and 0's.
Which =sumproduct() really likes.
Margaret wrote:
The numbers in column B are truly numbers. I think the
issues lies with the fact that in column A, I am not
looking for the exact value "Clean" but rather the
string "clean", where I want to match any cell containing
the text "clean" anywhere in the cell (including Clean,
cleaning, cleaned, She cleaned, etc.). If I
substitute "*clean*" in the formula it doesn't work,
either.
My formula for the example below was (ignore any line
wrapping)
=SUMPRODUCT((ISNUMBER(SEARCH("clean",A1:A5))),B1:B 5=1)
-----Original Message-----
What's in column B?
Are the values truly numbers or are they text that look
like numbers.
If you put a formula like:
=isnumber(b13)
where b13 looks like a 1 and A13 has "Clean" in it, what
do you get back?
I'm guessing that either the data isn't what you expect
or the formula you used
isn't correct.
If I guessed wrong about the data, maybe you should post
the formula you used.
(Bob's formula worked ok for me.)
Margaret wrote:
This returned a value of "0" (which is not correct).
Any
thoughts?
-----Original Message-----
=SUMPRODUCT(--(ISNUMBER(SEARCH("clean",A1:A100))), --
(B1:B100=1))
--
HTH
RP
(remove nothere from the email address if mailing
direct)
"Margaret" wrote
in message
...
I need to count the number of rows where cell A
includes
a specific text string AND cell B equals a specific
number. I am having trouble finding a working
solution.
Example
Column A Column B
Jim cleaned 1
Bob paints 1
Cleanup, Sue's 2
Jim swept 1
Cleaning by Joe 1
I want to count the rows where Column A contains the
text "clean" AND the value in Column B is 1. Correct
answer from example above would be 2.
Thanks in advance for any suggestions.
.
--
Dave Peterson
.
--
Dave Peterson
|