Excel 2007 Filter for NOT <> using Sumproduct

[BriCris]

I have 3 fields and I need to Filter then count using a NOT <>

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]LTR Score[/TD]
[TD]LTR Remarks[/TD]
[TD]Rep Satisfaction[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Still wait home[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Still home[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]wait home[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]This is home[/TD]
[TD]still or wait[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]this is also home[/TD]
[TD]wait[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]this is a Big HOME[/TD]
[TD]still[/TD]
[/TR]
</tbody>[/TABLE]

OK,

I want to filter out:
LTR Score <=7
LTR Remarks NOT <> Still or Wait --(ISNUMBER(SEARCH(<>"still",Test[LTR Remarks]))+ISNUMBER(SEARCH(<>"wait",Test[LTR Remarks]))>0)
Rep Satisfaction Search for Still or Wait --(ISNUMBER(SEARCH("still",Test[LTR Remarks]))+ISNUMBER(SEARCH("wait",Test[LTR Remarks]))>0)
Then count home.

The answer should be 3.

 

[Jonmo1]

The Opposite of ISNUMBER(SEARCH( would be ISERROR(SEARCH

Something like

=SUMPRODUCT(--(ISERROR(SEARCH("still",Test[LTR Remarks]))),--(ISERROR(SEARCH("wait",Test[LTR Remarks]))))

 

[BriCris]

I have 3 fields and I need to Filter then count using a NOT <>

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]LTR Score[/TD]
[TD]LTR Remarks[/TD]
[TD]Rep Satisfaction[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Still wait home[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Still home[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]wait home[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]This is home[/TD]
[TD]still or wait[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]this is also home[/TD]
[TD]wait[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]this is a Big HOME[/TD]
[TD]still[/TD]
[/TR]
</tbody>[/TABLE]

OK,

I want to filter out:
LTR Score <=7
LTR Remarks NOT <> Still or Wait --(ISNUMBER(SEARCH(<>"still",Test[LTR Remarks]))+ISNUMBER(SEARCH(<>"wait",Test[LTR Remarks]))>0)
Rep Satisfaction Search for Still or Wait --(ISNUMBER(SEARCH("still",Test[Rep Satisfaction]))+ISNUMBER(SEARCH("wait",Test[Rep Satisfaction]))>0)
Then count home.

The answer should be 3.

Had to make a changed (Don't see how to edit post)


I want to filter out:
LTR Score <=7
LTR Remarks NOT <> "Still" or "Wait"
Rep Satisfaction Search for "Still" or "Wait"
and then search for the word "home" in LTR Remarks

Thanks