| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text cells based on two criteria
I have a spreadsheet with many columns. I would like to count how many
occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
=Sumproduct(--(A1:A100="betty"),--(B1:B100="robert"))
-- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
It worked. Thanks. You rock!
"Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
guys,
this works well, but If there's a wild card in the formula it doesn't work. For example: =Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*")) thanks, "Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
SUMPRODUCT doesn't work with wild cards.
Try it like this: =SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100)))) -- Biff Microsoft Excel MVP "Jamie" wrote in message ... guys, this works well, but If there's a wild card in the formula it doesn't work. For example: =Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*")) thanks, "Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
IT WORKED!!!!
THANK YOU! "T. Valko" wrote: SUMPRODUCT doesn't work with wild cards. Try it like this: =SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100)))) -- Biff Microsoft Excel MVP "Jamie" wrote in message ... guys, this works well, but If there's a wild card in the formula it doesn't work. For example: =Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*")) thanks, "Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
Be advised that one of the pitfalls of this type of string matching is the
possibility of "false positives". --(ISNUMBER(SEARCH("robert",B1:B100))) That will find: robert roberta roberts robertson Basically *anything* that contains the substring "robert". -- Biff Microsoft Excel MVP "Jamie" wrote in message ... IT WORKED!!!! THANK YOU! "T. Valko" wrote: SUMPRODUCT doesn't work with wild cards. Try it like this: =SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100)))) -- Biff Microsoft Excel MVP "Jamie" wrote in message ... guys, this works well, but If there's a wild card in the formula it doesn't work. For example: =Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*")) thanks, "Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
Hi,
IN which case, one can use " robert " in the search function -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "T. Valko" wrote in message ... Be advised that one of the pitfalls of this type of string matching is the possibility of "false positives". --(ISNUMBER(SEARCH("robert",B1:B100))) That will find: robert roberta roberts robertson Basically *anything* that contains the substring "robert". -- Biff Microsoft Excel MVP "Jamie" wrote in message ... IT WORKED!!!! THANK YOU! "T. Valko" wrote: SUMPRODUCT doesn't work with wild cards. Try it like this: =SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100)))) -- Biff Microsoft Excel MVP "Jamie" wrote in message ... guys, this works well, but If there's a wild card in the formula it doesn't work. For example: =Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*")) thanks, "Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
But there might not be a space before or after robert in the data.
Pete On May 2, 12:06*am, "Ashish Mathur" wrote: Hi, IN which case, one can use " robert " in the search function -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "T. Valko" wrote in message ... Be advised that one of the pitfalls of this type of string matching is the possibility of "false positives". --(ISNUMBER(SEARCH("robert",B1:B100))) That will find: robert roberta roberts robertson Basically *anything* that contains the substring "robert". -- Biff Microsoft Excel MVP "Jamie" wrote in message ... IT WORKED!!!! THANK YOU! "T. Valko" wrote: SUMPRODUCT doesn't work with wild cards. Try it like this: =SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100)))) -- Biff Microsoft Excel MVP "Jamie" wrote in message ... guys, this works well, but If there's a wild card in the formula it doesn't work. For example: =Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*")) thanks, "Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. *I would like to count how many occurances there are of two criteria matching. *Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). *how many times does column A = betty AND column b = robert on the same row? *In this example, the answer would be one.- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count text cells based on two criteria
You can concatenate spaces:
--(ISNUMBER(SEARCH(" robert "," "&B1:B100&" "))) But this still isn't bulletproof: robert? robert, jim ,robert, -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... But there might not be a space before or after robert in the data. Pete On May 2, 12:06 am, "Ashish Mathur" wrote: Hi, IN which case, one can use " robert " in the search function -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "T. Valko" wrote in message ... Be advised that one of the pitfalls of this type of string matching is the possibility of "false positives". --(ISNUMBER(SEARCH("robert",B1:B100))) That will find: robert roberta roberts robertson Basically *anything* that contains the substring "robert". -- Biff Microsoft Excel MVP "Jamie" wrote in message ... IT WORKED!!!! THANK YOU! "T. Valko" wrote: SUMPRODUCT doesn't work with wild cards. Try it like this: =SUMPRODUCT(--(A1:A100="betty"),--(ISNUMBER(SEARCH("robert",B1:B100)))) -- Biff Microsoft Excel MVP "Jamie" wrote in message ... guys, this works well, but If there's a wild card in the formula it doesn't work. For example: =Sumproduct(--(A1:A100="betty"),--(B1:B100="*robert*")) thanks, "Tom Ogilvy" wrote: =Sumproduct(--(A1:A100="betty"),--(B1:B100="robert")) -- Regards, Tom Ogilvy "aet999" wrote: I have a spreadsheet with many columns. I would like to count how many occurances there are of two criteria matching. Example, If column A contains names of girls (amy, betty, susie, karen, betty) and column B contains names of boys (michael, robert, andrew, james, joseph). how many times does column A = betty AND column b = robert on the same row? In this example, the answer would be one.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
| counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
| count non blank cells which meet criteria in another column | Excel Worksheet Functions | |||
| Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
| conditional formating - Highlighting text cells based on sales res | Excel Discussion (Misc queries) | |||
Linear Mode
