Multiple array problem...pls help?
 

Ty..

next time post questions like this in another NG:
excel.worksheet.functions

this group is primarily for VBA programming...

1....
why didn't u use a PivotTable for your report?
should think that would have been a lot easier...

2...

{=if(COUNT('data entry complaints'!$N$2:$N$796=array!$A14*'data entry
complaints'!$B$2:$B$796=7)=0,"nil",COUNT('data entry complaints'!$N$2:$N
$796=array!$A14*'data entry complaints'!$B$2:$B$796=7))}


But I suggest :
{=COUNT('data entry complaints'!$N$2:$N$796=array!$A14*'data entry
complaints'!$B$2:$B$796=7)}

THEN format your cell like #,#,"nil"



Problem with using the if formula.. he'd have to compute it twice..
which would slow down your sheet (similar to if (count=0,"nil";count)

Using the second options gives "nil" for zero values... you'd get
exactly what you need with best speed.


again... look at PivotTables !!! they're a very powerfull tool and not
enough people use m (or know what they're about)




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Ty Williams wrote:

I have just begun to use the array formula in my work and it is very
powerful.
Hi,

I need to be able to report on a worksheet a specific tally of
criteria, and can do this in specific months by the use of an array.

I have made a report which looks like a yearly planner, with
departments listed on the left and the cells for the months giving
totals across the board. I use the following formula to do this:

=COUNT(IF('Data Entry Complaints'!$N$2:$N$796=Array!$A14,IF('Data
Entry Complaints'!$B$2:$B$796=7,0)))

Holding down CTRL+SHIFT+ENTER to give the curly brackets, I get a
count of the complaints for a specific section (A14 in the array,
during July in the example above).

However, there are also NIL returns forms which are submitted by
departments for months that have no complaints, and I need to
incorporate that reporting into my cell. I can not just assume that a
zero is a NIL report.

I would like it to follow a similar pattern, searching the same array
items but from entries in a nil report worksheet, and placing the word
"NIL" into the cell if the criteria matches. I cannot work out how to
do a "if then else" type of formula, to check if there is a match in
the nil workbook using the array data, and if so placing NIL in the
cell, but otherwise going onto the count formula I have above.

Can anyone please point me in the right direction?

Many thanks,

Ty