Count for multiple text criterias

[saneedshelp]

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

 

[Reitanos]

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.

 

[saneedshelp]

Yes, pretty much in table format. Showing comparison by county and type.

 

[T. Valko]

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

 

[T. Valko]

=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))))

 

[saneedshelp]

Thank you so much! I didn't try option 2, but option one works! Thanks for
your time!!