Using a custom Excel function in conjunction with SUMPRODUCT

dactechs
dactechs used Ask the Experts™
on
I need to count all the cells in a column containing a date between $C$2 and $D$2 which are highlighted with color 33.  I would like to be able to do this with a single-cell formula. I have not been having any luck.

The custom function is this:

Function GetCellColor(rRange As Range) As Long
     GetCellColor = rRange.Cells(1).Interior.ColorIndex ' Return the cell color.
End Function

Using it in a cell Formula like this works:

=(GetCellColor(Database!H2)=33)*(Database!H2>=$C$2)*(Database!H2<=$D$2)

Then I can drag it down all the rows and I get a 1 for all the H cells that are highlighted color 33 containing a date between $C$2 and $D$2; all others show 0. Then I sum the column in another cell.

But what I REALLY want is a single-cell formula using SUMPRODUCT to give me this sum, something like this:

=SUMPRODUCT((GetCellColor(Database!H:H)=33)*(Database!H:H>=$C$2)*(Database!H:H<=$D$2))

This always produces 0.

How can I write a function like GetCellColor to work in a situation like this?  It seems like there is something fundamentally different about functions that work in this fashion, and I just haven't got a clue.

I haven't figured out what Excel is doing behind the scenes in a case like this. I always assumed that it was iterating over the range, calling the function each time with the current cell passed in as a single-cell range.

Can anyone shed light on this for me?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi!
Your UDF will not give your an array, you need to re-write it to produce an array of the colour values.
This way (for one column-range):

Function GetRangeColor(rRange As Range) As Variant
     
     Dim vArr() As Variant
     Dim iCells As Long
     Dim i As Long
     
     
     iCells = rRange.Cells.Count
     ReDim vArr(1 To iCells, 1 To 1)
     i = 1
     
     For i = 1 To iCells
        vArr(i, 1) = rRange.Cells(i).Interior.ColorIndex ' Return the cell color.
     Next
     
     GetRangeColor = vArr

End Function

Open in new window


And the sum range is missing from your sumproduct:
=SUMPRODUCT((GetCellColor(Database!H:H)=33)*(Database!H:H>=$C$2)*(Database!H:H<=$D$2),   Database!H:H  )

See the small example file too.

Cheers,
Kris
color-sumif-example.xlsm

Author

Commented:
Thanks, that works if I use a specific range within a column.

It gives a correct count if I give a specific range in the formula  (if I use Database!H2:H10000, for example), but if I simply want to use a column reference (if I use Database!H:H), I get #VALUE! Can you explain the difference?

The values in the cells are either dates, or empty. They are only relevant for comparison to the start and end dates in $C$2 and $D$2.  The sumproduct is only summing the 1 or 0 products of the 3 conditions.

Commented:
The problem is that it is not possible to return more than 65536 record in array to the worksheet. So you can not use UDF with more than 65536 rows in this case. Not in Excel 2007, not in 2010.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
After testing in Excel 2010, it appears that leptonka's function can't return more than 65536 values. The moment I try 65537, it returns #VALUE!

But even if the code worked, you would find the SUMPRODUCT takes a long time to recalculate--18 seconds on my laptop.

For both the above reasons, I suggest that you limit the range.

Commented:
Yes, byundt, unfortunately it is a bug in Excel, we can do nothing with it - the range must be limited under 65536 rows.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial