Counting Cells by colour with an array formula
 

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

Counting Cells by colour with an array formula
 

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

"Stu" wrote in message
...
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