Counting Cells by colour with an array formula

[Stu]

Hi

I have a formula checking several criteria and giving me a count of
items that satisfy these criteria. This is the formula and has been
entered as an array formula.

=SUM(('Summary TFX'!$I$5:$I$540="SMAT")*('Summary TFX'!$E$5:$E$540=
(YEAR($B$2)))*('Summary TFX'!$C$5:$C$540=(WEEKNUM($B$2))))

this works fine but what I want to do is add another parameter to also
check for the interior colour of the cell. I've added a function -
cellcolourindex - which returns the interior colour but can't get this
to work in the formula. the new formula (which returns #VALUE) is
here and the formula auditing tool shows it failing when evaluating
the collcolour index section but Im unsure why. (this has also been
entered as an array formula)

=SUM((cellcolourindex('Summary TFX'!$I$5:$I$540)=4)*('Summary TFX'!$I
$5:$I$540="SMAT")*('Summary TFX'!$E$5:$E$540=(YEAR($B$2)))*('Summary
TFX'!$C$5:$C$540=(WEEKNUM($B$2))))

Is it possible to use functions within array formulae? If so can
anyone give me any clues as to why mine doesn't work?

TIA

Stu

 

[Charles Williams]

Hi Stu,

Its hard to say whats wrong without seeing the code for CellColourIndex,
but:

To make a UDF work inside an array formula it needs to return an array -
something like

Public Function CellColourIndex(theRange As Range) As Variant
Dim vArr() As Variant
Dim j As Long
Dim k As Long

Application.Volatile
On Error GoTo Fail:
ReDim vArr(1 To theRange.Rows.Count, 1 To theRange.Columns.Count)
For j = 1 To UBound(vArr)
For k = 1 To UBound(vArr, 2)
vArr(j, k) = theRange.Cells(j, k).Interior.ColorIndex
Next k
Next j
CellColourIndex = vArr
Exit Function
Fail:
CellColourIndex = CVErr(xlErrValue)
End Function

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com