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*"))

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*"))

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*"))

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*"))

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*"))

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*"))