What's the correct formula?
 

Hi all,
I'm looking for the correct formula for this expression.

=COUNTIF((Your_Range,"hat and coat") or COUNTIF(Your_Range,"hat")) and
(range,"New York")

The first two criteria are possible selections and the third is a must in
order to count the record as 1

My question is what would be the logical expression(formula) to show this.
--
--
Cheers

try
=SUMPRODUCT((K7:K8={"hat","coat"})*1)

--
Don Guillett
SalesAid Software

"zubee" wrote in message
...
Hi all,
I'm looking for the correct formula for this expression.

=COUNTIF((Your_Range,"hat and coat") or COUNTIF(Your_Range,"hat")) and
(range,"New York")

The first two criteria are possible selections and the third is a must in
order to count the record as 1

My question is what would be the logical expression(formula) to show this.
--
--
Cheers

try
=sumproduct(--(or(yourrange="hat and coat",yourrange="hat"),--(range="New
York"))
the --( changes the logical true false to numeric 1,0.
the arrays in each section must be the same size but wont work with the
shrrthand for full columns or rows (A:A wont work)

"zubee" wrote:

Hi all,
I'm looking for the correct formula for this expression.

=COUNTIF((Your_Range,"hat and coat") or COUNTIF(Your_Range,"hat")) and
(range,"New York")

The first two criteria are possible selections and the third is a must in
order to count the record as 1

My question is what would be the logical expression(formula) to show this.
--
--
Cheers

On Mon, 5 Sep 2005 07:50:09 -0700, "zubee"
wrote:

Hi all,
I'm looking for the correct formula for this expression.

=COUNTIF((Your_Range,"hat and coat") or COUNTIF(Your_Range,"hat")) and
(range,"New York")

The first two criteria are possible selections and the third is a must in
order to count the record as 1

My question is what would be the logical expression(formula) to show this.
--


=--(((COUNTIF(Your_Range,"hat and coat") + COUNTIF(Your_Range,"hat")) *
COUNTIF(range,"New York")) 0)

=IF(AND(OR(COUNTIF(Your_Range,"hat and coat"),
COUNTIF(Your_Range,"hat")),COUNTIF(range,"New York")),1,0)


--ron

Hi zubee,

the first OR condition can be realised via:

=COUNTIF((Your_Range,"hat*")

if you need to add the AND condition applying to another range, then use:

=SUMPRODUCT(ISNUMBER(SEARCH("hat",A1:A10))*(B1:B10 ="new york"))

or

=SUMPRODUCT(--ISNUMBER(SEARCH("hat",A1:A10)),--(B1:B10="new york"))

Regards,
KL

"Don Guillett" wrote in message
...
try
=SUMPRODUCT((K7:K8={"hat","coat"})*1)

--
Don Guillett
SalesAid Software

"zubee" wrote in message
...
Hi all,
I'm looking for the correct formula for this expression.

=COUNTIF((Your_Range,"hat and coat") or COUNTIF(Your_Range,"hat")) and
(range,"New York")

The first two criteria are possible selections and the third is a must in
order to count the record as 1

My question is what would be the logical expression(formula) to show
this.
--
--
Cheers