Need help with tough array formula

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
0
norm3380 (1)
1/7/2004 8:36:29 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
360 Views

Similar Articles

[PageSpeed] 1

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.
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Norm" <norm@shaw.ca> wrote in message
news:3a13c35d.0401071236.51fd4a35@posting.google.com...
> 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


0
Don
1/7/2004 8:53:32 PM
"Norm" wrote...
...
>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)))

--
To top-post is human, to bottom-post and snip is sublime.
0
hrlngrv (1990)
1/7/2004 8:56:53 PM
On Wed, 7 Jan 2004 14:53:32 -0600, "Don Guillett" <donaldb@281.com>
wrote:

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

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 
0
FG2605 (8)
1/7/2004 11:59:04 PM
On Wed, 07 Jan 2004 20:56:53 GMT, Harlan Grove<hrlngrv@aol.com> wrote:

>"Norm" wrote...
>..
>>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)))

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
0
FG2605 (8)
1/8/2004 12:24:35 AM
"Father Guido" <FG@SNL.NBC> wrote...
....
>>=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.
....

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)))


0
hrlngrv (1990)
1/8/2004 6:30:33 AM
On Thu, 08 Jan 2004 06:30:33 GMT, "Harlan Grove" <hrlngrv@aol.com>
wrote:

>"Father Guido" <FG@SNL.NBC> wrote...
>...
>>>=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.
>...
>
>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
0
FG2605 (8)
1/8/2004 7:19:49 AM
Reply:

Similar Artilces:

need help! very urgently needed
i am using an Activex control in a dialog based applicaton(VC++). created an member variable for that(myportcontroller). In a non-stati function i am able to call the Activex control methods using the membe variable and worked fine. But i need to call the activex control method in an static function. Here i cannot use the member variable as it is static function. So, i declared an object for Activex control class Using that object i called the methods. I had no compile time error But i am getting run time assertion error as "winocc.cpp line:345". Ca anybody help? I need very urgen...

New email setup help
ok here is what i have: currently have NT-4 box w/5.5, just setup new server w/2000 (we aren't going to active directory w2003, until fall).. we plan to setup new server with 5.5, then upgrade in fall.. Questions: how (what steps) to setup 2nd site, as we are only going to use NT box, until new box is setup, then shut down NT.. can we have both boxes existing at sametime ? will they share the database? or shall i say can they? is the database all i need to copy over? anything else anybody can think of will be appreciated... AL "Al Smith" <ascs_99@yahoo.com&...

help setting up windows mail
Hello! I am trying to set up my windows mail using an @live.com e-mail address, yet I cannot find the POP and/or STMP information for @live addresses anywhere. What do I enter in these two fields? Thank you in advance! To follow up with more information about my problem, I found the site: http://mailcall.spaces.live.com/Blog/cns!CC9301187A51FE33!49799.entry?sa=115122547 I thought I would be all set to go. I used the POP3 name of: pop3.live.com (Port 995) When I leave the (Port 995) on it, it says it is an invalid POP account. When I do it without the (Port 995), ...

need this answered faster than on other forum
I have win 7 home premium 64 bit. Just got the laptop and all of a sudden when I right click on desktop and choose either personalize or screen resolution it just hangs, the cursor just shows as being busy and you can't click on anything else on the desktop. If I end process of the dllhost.exe *32 the busy cursor stops but still no personalize page or screen resolution. I asked this on a win 7 group but they aren't too quick to answer on there. I did try to install corel paintshop X2 but it wouldn't install right so I uninstalled what files it did install. That...

Help with links in one worksheet please
Hi I have made up a worksheet that will work out all my takings & expenses for the week. I have then made a totals sheet which I would like to add up all the Weekly totals for the year. I have done all the links from the Weekly sheet to the totals sheet and they all work fine. As I will have 52 weeks how do I copy the links I have made for week one (with the relevant changes for that weekly sheet) in the totals sheet? I hope you can understand this Any web sites that may help? Thanks for looking Derek One way is to make a list of your sheet names in col A and use or a modifi...

Text Box with conditional formula 11-27-09
Is there any way to have a conditional text box? When I click on a text box the conditional formatting option is greyed out. Is there a way around this? In an earlier post I there was a reply but that created a picture and what I'm looking for is a conditionally formatable text box. Help please .... K -- Keyrookie ...

Releasing arrays?
Hi, when using arrays in a VBA sub, is it necessary to explict release memory at the end of procedure? If so, how to do this? I have tried Set myArr = nothing but it did not work. Thank you, Siri -- SiriS ------------------------------------------------------------------------ SiriS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7995 View this thread: http://www.excelforum.com/showthread.php?threadid=494563 Hi SiriS, > If so, how to do this? I have tried > Set myArr = nothing > but it did not work. Try: Erase myAyy --- Regards, Norm...

can i find and replace formula in a chart
i need to change the formulas in a number of charts from say january to february, can this be doen if so how? Jon Peltier has instructions for changing the series formulas in a chart: http://www.peltiertech.com/Excel/Charts/ChgSrsFmla.html At the bottom of the page, there's a sample file you can download, to make the process easier. timjimb wrote: > i need to change the formulas in a number of charts from say january to > february, can this be doen if so how? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Formula to calculate Latest Cost (inventory valuation)
I have a worksheet that contains the daily purchases for 4 products. column B contains the products column D contains the purchase price column K contains the division column J contains the day of the month I need a formula that will pull the latest purchase price by product by division. In other words the last purchase for the month and the associated purchase price. Thanks -- col You can do that with this array* formula: -- Best Regards, Luke M "Curtis" <curtis.eadie@yahoo.ca.(do not spam)> wrote in message news:61160B81-7277-4C97-8...

Help sorting dates in Excel
Hi group, I have an Excel spreadsheet. One of the columns is "Date" and all of the data is in the format "April, 2002" or "December, 2004" etc. I am trying to sort on this field, but Excel sorts alphabetically, putting December before February, etc. I have tried to edit the format of the date field to make it all numbers (to help with the sort), but I'm not finding the right one. Any tips on how to sort this? Thanks! Aaron Hi you have to store the values as real date values in Excel. Of yourse you can apply a custom format for these date like DD MMMM...

Help #21
I am using =INT((Current Date - DOB)/365.25) to count age... I want to use =SUMPRODUCT(--(Type="D"),--(Age="25")) It shows ZERO when I do this... I'm guessing its because there is a formula calculating the information in the age column. Any ideas how I can combine these data and find out how many twenty-five year olds bought product D? If you're calculating age, the results will be numbers. Try =25 rather than ="25" in your SUMPRODUCT formula. HTH Jason Atlanta, GA >-----Original Message----- >I am using > >=INT((Current Da...

Please, HELP!
Dear sirs! Please, help me! I very need a money! Send me $1 to WebMoney Z264980808284. Best regards. Sure, when did you get out of the crazy house! My mom always said that there are more out than in! ...

Print help
When I try to print a portfolio report, it opens the preview in Word instead of Internet Explorer. How can I change this. Thanks In that nothing I do seems to create the conditions where Print Preview is enabled--and the word "preview" is not even to be found in online help--I can't say. "JimJ" <JimJ@discussions.microsoft.com> wrote in message news:B633088B-A3A5-44BC-9444-DB79719CEFC5@microsoft.com... > When I try to print a portfolio report, it opens the preview in Word > instead > of Internet Explorer. How can I change this. What's happ...

Drop down list or combo box help needed
I am hopeful that someone will know how to accomplish this: In a single column of a worksheet 1. Create a selection list (or choices) which can populate the cells of that column 2. Create a list box or combo box to hold the choices 3. Have that list or combo box appear when one of the individual cell of this column becomes active. I've had several attempts at this, but haven't accomplished this yet. I've created these in the past in Word, but I know it is more complex than what I've done in Word. Any help you can provide is greatly appreciated!!!!!! Sincerely, mar...

need info on failed login attempts
when you or someone attempts to log under a user id with the wrong password, then successfully logs in, it tells you after that particular login, the number of failed login attempts before the successful log in. where in the database does it store this information? thank you, kevin Cashier.FailedLogonAttempts Rob "kskinne" wrote: > when you or someone attempts to log under a user id with the wrong password, > then successfully logs in, it tells you after that particular login, the > number of failed login attempts before the successful log in. where in the > ...

Make Worksheet Reference in a Formula Relative
I have a worksheet where a number of cells get their value from the previous workheet using the formula format: ='worksheet(Y)'!$X$N When I make a copy of the worksheet and move it to the end, the result in the new sheet refers to the original sheet. How can I make the formula automatically refer to the cell in the preceeding sheet, as opposed to the original sheet? I want to copy the sheet about 30 times in the same workbook, and each new sheet needs to refer to the one preceeding, not the original sheet. "Don Kirk" wrote... ... >How can I make the formul...

Creating a Formula to Format Column automatically? #3
Can I record it as a macro or anything like that? I have to run it o five sheets. I've tried to conditional formatting, and it works only o one cell, unless I'm doing it wrong -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 not sure what you are trying to do but conditional formating can be coped and pasted. you have also conditional format an entire column/row by selected the en...

Excel changes file name to numeric name when saving --HELP
When DFS is turned on, the name changes to a numeric name that includes date and timestamp. Enviorment Win2K SP4, replicating to a Win2k3 server. When Excel saves a file, it first saves it under a temporary name. If there are no errors during the save, it deletes the original file and renames the new version. Sounds like your network settings/permissions are not allowing Excel to delete the original file. On Fri, 4 Feb 2005 07:57:06 -0800, Greg Rinaldi <GregRinaldi@discussions.microsoft.com> wrote: >When DFS is turned on, the name changes to a numeric name that includes date >...

Formula displays instead of value
Corporate sends Excel spreadsheets that do not allow entry of a formula. If a formula is entered it shows with the leading = sign. I cannot get it to display the value of the formula. Have tried formatting the cells to number, but that does not work. If I copy the formula to a blank spreadsheet it displays the value properly. Please help with suggestions. thanks, Ron Hi after changing the format to 'Number' or 'General' re-enter the formula. If this does not help check under 'Tools - Options - View' that 'formulas' is unchecked -- Regards Frank Kabel Frank...

Need Assistance With Defining Column Value in a View
In view design I wish a row to calculate the UnitDiscountAmt by multiplying the UnitPrice * DiscountRate if DiscountRate >0, Else just show the UnitPrice. Now if I were doing this in Access I would IIf([DiscountRate]=0, [UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the view? CASE (which is more generic than IIF and also ANSI SQL). Something like: SELECT col1 ,CASE WHEN DiscountRate > 0 THEN UnitPRice*DiscountRate ELSE Unitprice AS colname END ,col3 FROM ... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.as...

Need Assistance on mail delivery between two domains
I have two domains, domain A running Win2k3 and Exchange 2k3 and domain B running Win2k and Exchange 5.5. I created a two-way trust between them and validated the trusts. When I try to email from domain A to domain B, I get an NDR stating "The following recipient cannot be reached; you do not have permission to send to this recipient; smtp; 550 5.7.1 unable to relay for the recipient." When I try to email from domain B to domain A, I get an NDR stating "No transport providor was avalable for delivery to this recipient." This is a new test lab. When I had my old ...

Report help 06-29-07
I have like 20 tables(each table is a member number) that have all the same titles but different data. Is there any way to do a report with different member numbers? or do I have to do a huge table? "Maggie" <schmidt.margaret@gmail.com> wrote in message news:1183133346.028677.60360@k29g2000hsd.googlegroups.com... >I have like 20 tables(each table is a member number) that have all > the same titles but different data. Is there any way to do a report > with different member numbers? or do I have to do a huge table? It should all be in one table. On Jun 29,...

Formula for calc diff between two julian dates
Looking for the formula for calc diff between two julian dates. Worksheet is as follows: J-2 = order date K-2 = receive date L-2 = Lag time :confused -- saltr ----------------------------------------------------------------------- saltrm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2862 View this thread: http://www.excelforum.com/showthread.php?threadid=48288 DateDiff("d", "J2", "K2") "saltrm" wrote: > > Looking for the formula for calc diff between two julian dates. > > Worksheet is as follows: >...

Query Formula (stones and pounds to kg's)
I have a table with 2 weight fields, stones and pounds. Is there a formula that I could use to convert this into a single kg field. I would be doing this in a query There are many answers on google but they seem all to be for excel. Thank you not perfectly clear but can't you just add the two converted values? [Kg] field source =[stone]* 6.35 + [Lbs] * 2.2 "Karren Lorr" wrote: > I have a table with 2 weight fields, stones and pounds. > Is there a formula that I could use to convert this into a single kg field. > > I would be doing this in a que...

SUMIF Formula not working
Hi All I need some help with a SUMIF formula. I created a formula in a spreadsheet to count the number of entried where the specified values in three columns met the criteria. Having got it to work I decided to create individual spreadsheets to be able to report on different criteria. The issue I am having is that in some cases I get an #N/A error in some cells which is indicating it cannot find a particular value. The formula I am using is as follows: SUM(IF(N$2:N$30000=B3,IF(Y$2:Y$30000="Cancel Appointment",IF(AA$2:AA$30000="Service Provider Clinician&quo...