Which formula to use? countif, sumif, sumproduct

I would like to count the number of records that appear in the same column
for example column "A"
A
hat and coat
coat
hat
hat and coat
gloves

The formula that i am looking for would answer the question, how many
records contain ("hat and coat" and "hat") the answer would be 3 records.
Any help would be greatly appreciated!

--
Cheers

=SUMIF(A:A,"*hat*")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"zubee" <[email protected]> wrote in message
news:[email protected]...
> I would like to count the number of records that appear in the same column
> for example column "A"
> A
> hat and coat
> coat
> hat
> hat and coat
> gloves
>
> The formula that i am looking for would answer the question, how many
> records contain ("hat and coat" and "hat") the answer would be 3 records.
> Any help would be greatly appreciated!
>
> --
> Cheers

=COUNTIF(Your_Range,"hat and coat")+COUNTIF(Your_Range,"hat")

or

=SUMPRODUCT((Your_Range="hat and coat")+(Your_Range="hat"))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"zubee" <[email protected]> wrote in message
news:[email protected]...
>I would like to count the number of records that appear in the same column
> for example column "A"
> A
> hat and coat
> coat
> hat
> hat and coat
> gloves
>
> The formula that i am looking for would answer the question, how many
> records contain ("hat and coat" and "hat") the answer would be 3 records.
> Any help would be greatly appreciated!
>
> --
> Cheers

meant COUNTIF

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message
news:ehvyLN%[email protected]...
> =SUMIF(A:A,"*hat*")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "zubee" <[email protected]> wrote in message
> news:[email protected]...
> > I would like to count the number of records that appear in the same
column
> > for example column "A"
> > A
> > hat and coat
> > coat
> > hat
> > hat and coat
> > gloves
> >
> > The formula that i am looking for would answer the question, how many
> > records contain ("hat and coat" and "hat") the answer would be 3
records.
> > Any help would be greatly appreciated!
> >
> > --
> > Cheers
>
>