Count for multiple text criterias

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

saneedshelp - 06 May 2008 20:15 GMT

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 to this Message

Reitanos - 06 May 2008 20:32 GMT

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 <saneedsh...@discussions.microsoft.com>
wrote:

> I would like a count for County A with type Lend, AB owned.
>
[quoted text clipped - 11 lines]
>
> =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*"))

Reply to this Message

saneedshelp - 06 May 2008 21:08 GMT

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

> I have one question for you: do you need to have all the counts
> displayed at all times?
[quoted text clipped - 22 lines]
> >
> > =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*"))

Reply to this Message

T. Valko - 06 May 2008 21:50 GMT

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

Signature

Biff
Microsoft Excel MVP

>I would like a count for County A with type Lend, AB owned.
>
[quoted text clipped - 12 lines]
>
> =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*"))

Reply to this Message

T. Valko - 06 May 2008 22:08 GMT

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

Signature

Biff
Microsoft Excel MVP

> Try one of these:
>
[quoted text clipped - 20 lines]
>>
>> =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*"))

Reply to this Message

saneedshelp - 06 May 2008 22:52 GMT

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

> > =SUMPRODUCT(--(A2:A8="A"),--(ISNUMBER(SEARCH("lend",B2:B8))),--(ISNUMBER(SEARCH("AB
> > owned",B2:B8))))
[quoted text clipped - 27 lines]
> >>
> >> =SUMPRODUCT(--(May!$X$2:$X$429="A"),--(May!$AF$2:$AF$429="*Lend*AB*"))

Reply to this Message