T
Ty Williams
Hi,
I have just begun to use the array formula in my work and it is very
powerful. (I am not a very advance user at all, but am learning all
the time).
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.
The report looks like a year planner, with the departments on the
left, and the months in cells alongside to give an accurate calendar
type view.
=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 would like it to follow a similar pattern, searching the same array
items but in a nil report worksheet within the same file, 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
I have just begun to use the array formula in my work and it is very
powerful. (I am not a very advance user at all, but am learning all
the time).
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.
The report looks like a year planner, with the departments on the
left, and the months in cells alongside to give an accurate calendar
type view.
=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 would like it to follow a similar pattern, searching the same array
items but in a nil report worksheet within the same file, 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