| 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. |
| 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) | |||
Hybrid Mode
