Counting rows based on criteria in multiple cells

  • Thread starter Thread starter Margaret
  • Start date Start date
M

Margaret

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.
 
=SUMPRODUCT(--(ISNUMBER(SEARCH("clean",A1:A100))), --(B1:B100=1))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
One way is to Concatenate the two into a third column and then do a count
on that column for the combination you wish.

Vaya con Dios,
Chuck, CABGx3
 
Did you overcome the wrap-around that the NG added.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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.)
 
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:B5=1)
 
Yes. The exact formula I used for the example I gave was:
=SUMPRODUCT((ISNUMBER(SEARCH("clean",A1:A5))),B1:B5=1)
 
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.
 
Oops, my bad (apologies to Bob)! I didn't recognize the
double minus signs, thought they were some kind of space
indicator that I should delete...Formula as given below
works fine.

Thanks!
 
Sorry, my error. I altered the formula due to a
misunderstanding on my part. The formula works as it was
originally given.
 
Back
Top