Need help with tough array formula

[Norm]

Hi,

I'm using Excel2002 with Windows2000.

I have a report where I need to extract counts based on 4 criteria.

I have this so far...

{=SUM(($F$2:$F$500="Ready to
Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF"))}

which gives me the first 3 criteria to meet, but the fourth criteria
is to reduce this count based on whether the fourth criteria is met.
I.e. I want to ignore the first three criteria if the fourth criteria
is also met. The fouth criteria is if the word 'vortex' appears
anywhere in its range.

These are what I've tried so far, none of these work, even though I
know there are instances of 'vortex' in the cell.

{=SUM(($F$2:$F$500="Ready to
Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF")*
($O$2:$O$500<>"*vortex*))}

{=SUM(($F$2:$F$500="Ready to
Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF")*
($O$2:$O$500="<>"*vortex*"))}

{=SUM(($F$2:$F$500="Ready to
Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF"))-
SUM(($F$2:$F$500="Ready to
Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF")*
($O$2:$O$500="*vortex*))}

I need to find a way to do this to impress my boss, unfortunately I
think it's too late already. I think my last formula is the best bet
if I can find a way to count the cells containing 'vortex' in them
that meet the other 3 criteria.

Just counting the number of 'vortex' in the range won't work as it
needs to be applied against the other 3 criteria as well. I'm hoping
for a formula solution I can build on for the future.

Any/all suggestions are most welcome.

Thanks in advance for your help!

Norm

 

[Don Guillett]

just add

{=SUM(($F$2:$F$500="Ready to
Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF")*(other<>"Vortex"))}

BTW if you change sum to sumproduct you do NOT have to array enter.

 

[Harlan Grove]

...
...

I have this so far...

{=SUM(($F$2:$F$500="Ready to Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF"))}

which gives me the first 3 criteria to meet, but the fourth criteria ...
I.e. I want to ignore the first three criteria if the fourth criteria
is also met. The fouth criteria is if the word 'vortex' appears
anywhere in its range.

In it's range? Meaning as text anywhere in one cell corresponding to one cell in
each of the different criteria or anywhere in any of the cells in some other
range?

These are what I've tried so far, none of these work, even though I
know there are instances of 'vortex' in the cell.

{=SUM(($F$2:$F$500="Ready to Verify")*($M$2:$M$500=$Z$1)*($N$2:$N$500="TF")
*($O$2:$O$500<>"*vortex*))}

...

Looks like the former interpretation. Excel string comparison operators don't
support wildcards. Change this to

=SUMPRODUCT(($F$2:$F$500="Ready to Verify")*($M$2:$M$500=$Z$1)
*($N$2:$N$500="TF")*ISNUMBER(SEARCH("vortex",$O$2:$O$500)))

 

[Father Guido]

=SUM(($F$2:$F$500="Ready to

Thanks, but if I use my range ($O$2:$O$500) in place of other above, I
get 0 for the answer when I array enter. I get the same thing if I use
SUMPRODUCT instead of array entering it. If I enter exactly as above I
get #NAME? for an answer, which I'm sure isn't what you meant anyway.

Norm

 

[Father Guido]

...
..

In it's range? Meaning as text anywhere in one cell corresponding to one cell in
each of the different criteria or anywhere in any of the cells in some other
..

Looks like the former interpretation. Excel string comparison operators don't
support wildcards. Change this to

=SUMPRODUCT(($F$2:$F$500="Ready to Verify")*($M$2:$M$500=$Z$1)
*($N$2:$N$500="TF")*ISNUMBER(SEARCH("vortex",$O$2:$O$500)))

Guess I wasn't clear enough. Hopefully this will help.

I have a number of columns in a report, for simplicity I'll just list
the four I need below.

$Z$1 = 12.1
F M N O
1 Ready to Verify 12.1 TF Vortex 12.1 not enabled
2 Ready to Verify 12.1 TF Product Test:Vortex Sanity Tests3
3 Ready to Verify 12.1 TF Power Palette: Test Setting

In the above small sample, I need to get an answer of 1. Cells F3, M3,
and N3 match the first 3 criteria AND cell O3 doesn't contain the word
vortex anywhere in it.

Rows 1 & 2 also meet the first 3 criteria, but their cells in col O do
have the word vortex somewhere within them, which invalidates the entire
row and should be treated as 0 from my point of view.

Thanks in advance!

Norm

 

[Harlan Grove]

Guess I wasn't clear enough. Hopefully this will help.

....

You were clear. I screwed up. Change the formula above to

=SUMPRODUCT(($F$2:$F$500="Ready to Verify")*($M$2:$M$500=$Z$1)
*($N$2:$N$500="TF")*ISERROR(SEARCH("vortex",$O$2:$O$500)))

 

[Father Guido]

...

You were clear. I screwed up. Change the formula above to

=SUMPRODUCT(($F$2:$F$500="Ready to Verify")*($M$2:$M$500=$Z$1)
*($N$2:$N$500="TF")*ISERROR(SEARCH("vortex",$O$2:$O$500)))

Thanks Harlan for your efforts. This time I screwed up, I copied
the formula including the > symbol. It works excellent, once I
realized my mistake. Thanks very much for your help, and ignore
my new message asking for more help.

Thanks again!!!

Norm