Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Margaret
 
Posts: n/a
Default Counting rows based on criteria in multiple cells

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.
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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.



  #3   Report Post  
CLR
 
Posts: n/a
Default

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


"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.



  #4   Report Post  
Margaret
 
Posts: n/a
Default

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.



.

  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

Did you overcome the wrap-around that the NG added.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Margaret" wrote in message
...
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.



.





  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #7   Report Post  
Margaret
 
Posts: n/a
Default

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
.

  #8   Report Post  
 
Posts: n/a
Default

Yes. The exact formula I used for the example I gave was:
=SUMPRODUCT((ISNUMBER(SEARCH("clean",A1:A5))),B1:B 5=1)

-----Original Message-----
Did you overcome the wrap-around that the NG added.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Margaret" wrote

in message
...
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.


.



.

  #9   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #10   Report Post  
Margaret
 
Posts: n/a
Default

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!

-----Original Message-----
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
.



  #11   Report Post  
Margaret
 
Posts: n/a
Default

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

-----Original Message-----
Yes. The exact formula I used for the example I gave was:
=SUMPRODUCT((ISNUMBER(SEARCH("clean",A1:A5))),B1: B5=1)

-----Original Message-----
Did you overcome the wrap-around that the NG added.

--

HTH

RP
(remove nothere from the email address if mailing

direct)


"Margaret" wrote

in message
...
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.


.



.

.

  #12   Report Post  
Bob Phillips
 
Posts: n/a
Default

See Dave helped you get it sorted, so that's good :-)

Bob


"Margaret" wrote in message
...
Sorry, my error. I altered the formula due to a
misunderstanding on my part. The formula works as it was
originally given.

-----Original Message-----
Yes. The exact formula I used for the example I gave was:
=SUMPRODUCT((ISNUMBER(SEARCH("clean",A1:A5))),B1: B5=1)

-----Original Message-----
Did you overcome the wrap-around that the NG added.

--

HTH

RP
(remove nothere from the email address if mailing

direct)


"Margaret" wrote

in message
...
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.


.



.

.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple rows of data on a single axis (charting) ramseysgirl Charts and Charting in Excel 8 December 29th 04 07:00 PM
How can I combine multiple cells in Excel? jallbright24 Excel Discussion (Misc queries) 1 November 29th 04 05:54 PM
background formatting across multiple cells Casper Excel Discussion (Misc queries) 0 November 26th 04 12:18 PM
How do I extract cells from multiple workbooks Trevor Excel Discussion (Misc queries) 1 November 25th 04 11:59 PM


All times are GMT +1. The time now is 11:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"