| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count for multiple text criterias
I would like a count for County A with type Lend, AB owned.
County Type A Lend by exit A Lend by exit, estate, AB owned A Lend, probate CD A Lend by exit, estate, AB owned A Lend by exit, inv, AB owned B Lend B Lend by exit, inv, AB owned Have Excel 2003; doing counts in a diffent tab. What am I doing wrong? Is it due to the wildcard characters? Open to suggestions. =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*")) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count for multiple text criterias
I have one question for you: do you need to have all the counts
displayed at all times? If not, a cool alternative is to use Filter/Autofilter and have a SUBTOTAL(3,range) command on the page. This option lets you filter the data any way you please and see the total for only what is displayed - the SUBTOTAL function ignores any rows hidden by the filter. Oh, and that 3 in the function operates as a COUNTA; 2 is for COUNT and 9 is for SUM. On May 6, 3:15 pm, saneedshelp wrote: I would like a count for County A with type Lend, AB owned. County Type A Lend by exit A Lend by exit, estate, AB owned A Lend, probate CD A Lend by exit, estate, AB owned A Lend by exit, inv, AB owned B Lend B Lend by exit, inv, AB owned Have Excel 2003; doing counts in a diffent tab. What am I doing wrong? Is it due to the wildcard characters? Open to suggestions. =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*")) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count for multiple text criterias
Yes, pretty much in table format. Showing comparison by county and type.
"Reitanos" wrote: I have one question for you: do you need to have all the counts displayed at all times? If not, a cool alternative is to use Filter/Autofilter and have a SUBTOTAL(3,range) command on the page. This option lets you filter the data any way you please and see the total for only what is displayed - the SUBTOTAL function ignores any rows hidden by the filter. Oh, and that 3 in the function operates as a COUNTA; 2 is for COUNT and 9 is for SUM. On May 6, 3:15 pm, saneedshelp wrote: I would like a count for County A with type Lend, AB owned. County Type A Lend by exit A Lend by exit, estate, AB owned A Lend, probate CD A Lend by exit, estate, AB owned A Lend by exit, inv, AB owned B Lend B Lend by exit, inv, AB owned Have Excel 2003; doing counts in a diffent tab. What am I doing wrong? Is it due to the wildcard characters? Open to suggestions. =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*")) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count for multiple text criterias
Try one of these:
=SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend",B2:B8))),--(ISNUMBER(SEARCH("AB owned",B2:B8)))) =SUMPRODUCT(--(A2:A8="A"),--(MMULT(--ISNUMBER(SEARCH({"Lend","AB owned"},B2:B8)),{1;1})=2)) -- Biff Microsoft Excel MVP "saneedshelp" wrote in message ... I would like a count for County A with type Lend, AB owned. County Type A Lend by exit A Lend by exit, estate, AB owned A Lend, probate CD A Lend by exit, estate, AB owned A Lend by exit, inv, AB owned B Lend B Lend by exit, inv, AB owned Have Excel 2003; doing counts in a diffent tab. What am I doing wrong? Is it due to the wildcard characters? Open to suggestions. =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*")) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count for multiple text criterias
=SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend",B2:B8))),--(ISNUMBER(SEARCH("AB
owned",B2:B8)))) That can be reduced to: =SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend*AB owned",B2:B8)))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try one of these: =SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend",B2:B8))),--(ISNUMBER(SEARCH("AB owned",B2:B8)))) =SUMPRODUCT(--(A2:A8="A"),--(MMULT(--ISNUMBER(SEARCH({"Lend","AB owned"},B2:B8)),{1;1})=2)) -- Biff Microsoft Excel MVP "saneedshelp" wrote in message ... I would like a count for County A with type Lend, AB owned. County Type A Lend by exit A Lend by exit, estate, AB owned A Lend, probate CD A Lend by exit, estate, AB owned A Lend by exit, inv, AB owned B Lend B Lend by exit, inv, AB owned Have Excel 2003; doing counts in a diffent tab. What am I doing wrong? Is it due to the wildcard characters? Open to suggestions. =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*")) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count for multiple text criterias
Thank you so much! I didn't try option 2, but option one works! Thanks for
your time!! "T. Valko" wrote: =SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend",B2:B8))),--(ISNUMBER(SEARCH("AB owned",B2:B8)))) That can be reduced to: =SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend*AB owned",B2:B8)))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try one of these: =SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend",B2:B8))),--(ISNUMBER(SEARCH("AB owned",B2:B8)))) =SUMPRODUCT(--(A2:A8="A"),--(MMULT(--ISNUMBER(SEARCH({"Lend","AB owned"},B2:B8)),{1;1})=2)) -- Biff Microsoft Excel MVP "saneedshelp" wrote in message ... I would like a count for County A with type Lend, AB owned. County Type A Lend by exit A Lend by exit, estate, AB owned A Lend, probate CD A Lend by exit, estate, AB owned A Lend by exit, inv, AB owned B Lend B Lend by exit, inv, AB owned Have Excel 2003; doing counts in a diffent tab. What am I doing wrong? Is it due to the wildcard characters? Open to suggestions. =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*")) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count with multiple criterias | Excel Worksheet Functions | |||
| Count single Text in cells with multiple text entries | Excel Discussion (Misc queries) | |||
| DCOUNT WITH MULTIPLE CRITERIAS | Excel Worksheet Functions | |||
| Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions | |||
| countif help for multiple criterias | Excel Worksheet Functions | |||
Linear Mode
