Home 
Search 
Today's Posts 
#1




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




=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




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




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




Did you overcome the wraparound 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




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




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




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 wraparound 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




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




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

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Multiple rows of data on a single axis (charting)  Charts and Charting in Excel  
How can I combine multiple cells in Excel?  Excel Discussion (Misc queries)  
background formatting across multiple cells  Excel Discussion (Misc queries)  
How do I extract cells from multiple workbooks  Excel Discussion (Misc queries) 