Excel Arrays

[izzyq]

What is the syntax when trying to use multiple criteria for filtering data in an area. The way I have been doing it is like so: =SUM((CostMonth_2000)*(WC_2000<>&quot;ST&quot*(WC_2000<>&quot;PE&quot*(Mining_2000=&quot;1410&quot*(Mining_2000=&quot;1411)*(Main_2000=&quot;910&quot*(Cost_2000))/1000000. The problem is when I try to do multiple Mining_2000 filtering it keeps giving me a value of zero. For instance when I make it equal to just 1410 a value is returned, but when I put both of them in it returns zero. I know there is data for both 1410 and 1411 because when you do them on there own a value is returned. Help Me Obi Wan Kenobi, You're My Only Hope.

 

[DaleWatson123321]

Izzy,

There's a reasonably good expectation that someone will be able to help you out with your &quot;Array filtering&quot;.

However, if it &quot;doesn't happen to work out&quot; (or even if it does), might I suggest you consider another type of &quot;filtering&quot; that is VERY POWERFUL, and yet is OFTEN overlooked. This is found under Data - Filter - Advanced Filter.

If you've tried this option from the menu, you've probably encountered some problems. This is due to there being a couple of &quot;bugs&quot; ... 1) an &quot;invalid&quot; ERROR message that says you can NOT copy data to a separate sheet, and 2) that you can NOT have your &quot;criteria&quot; on a separate sheet. There are other &quot;quirks&quot; as well. In spite of these, Excel's &quot;database functionality&quot; is nonetheless VERY powerful. This includes database formulas as well as the extraction of data, based on selective criteria.

I have no doubt that, with a little help, you will find it MUCH easier to create &quot;complex&quot; criteria using this &quot;Advanced Filter&quot; method.

If at some time you want to &quot;take me up on my challenge&quot; - by sending me that portion of your file (with sensitive data replaced with fictitious data if required) - you can reach me at the following address.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GlennUK]

To do the filtering that you want, use this formula ..

=SUM((CostMonth_2000)*(WC_2000<>&quot;ST&quot*(WC_2000<>&quot;PE&quot*((Mining_2000=&quot;1410&quot+(Mining_2000=&quot;1411))*(Main_2000=&quot;910&quot*(Cost_2000))/1000000

Notice that the Mining_2000 comparisons for 1410 and 1411
are added together, as logical comparisons like this return
arrays of 0's and 1's, where 0's exist for false conditions
and 1's are returned for true conditions. Adding the results together like this is equivalent to asking for Mining_2000 equal 1410 OR 1411.

Hope this helps.
Glenn.