Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with formula | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Offset & Match Formula Shows Duplicates | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |