Counting Conditionally Formatted Coloured Cells

Hi

SUMMARY
I am looking to count only cells which have been conditionally formatted too little success.


BACKGROUND
I have been working on a worksheet (attached) in Excel 2007 which is looking to show multiple cases and payments on a month-by-month basis.

The first step was to only show the 'active' periods of each case, which I managed through conditional formatting, the CF formula is:

=AND($F6>H$5,$E6<I$5)

E6 is the case Start Date
F6 is the case End Date
H5 is month 1 (e.g. May 2013)
I5 is month 2 (e.g. Jun 2013)

When applied (to cases with dates), the 'active' periods are highlighted in blue (37, R:153/G:204/B:255)

My next step was to then calculate the monthly income (the figure will differ for each case, but fixed across the period of each case) located in column D. I then copied this across the entire spreadsheet (=$D#). Unfortunately doing a sum to count the column would count all cells, and not those that which have been CF. In the attached worksheet, the text has been conditionally formatted to show up as black, otherwise the text is white.

I thus began looking around for ways to count the content of cells that have been conditionally formatted to a certain colour. I have tried several methods (from across the web, and on this forum), and have come to the conclusion that the SUMBYCOLORINDEX method from http://www.cpearson.com/excel/CFColors.htm, but when I put in the following formula in row 3 (highlighted in yellow only to draw attention):

=SUMBYCFCOLORINDEX(H6:H55,37)

H6:H55 is the column of payments
37 is the colour (blue, 37, R:153/G:204/B:255, http://dmcritchie.mvps.org/excel/colors.htm) of the CF cells

I get a #NAME? error despite having Chip Pearson’s VB script active. In row 2 above I have done a quick count to show what the row 3 column CF sums should come to.

For some reason I am unable to attach any files to this thread (either *.xlsm, *.xlsx, zip or .jpg (I get a 407 error?), so I have uploaded a picture here:

A screenshot of the worksheet and script is visible here: http://i4.minus.com/iJmIaE1zx0LsW.jpg


CONCLUSION
I would be most grateful if anyone could provide me with assistance in solving this dilemma.

Why not count the cells based upon the condition that makes them a certain color, using the countif function.

I agree with Alan, but am not sure based on the picture how you are summing (by column?)

As to why you're getting #Name, I believe it's because in your UDF you are referencing another function "ColorIndexofCF" You need to add that code to the module too.

Please Login or Register  to view this content.

=SUMIFS(I$:6:I$50, $E$6:$E$50,"<"& I$5, $F$6:$F$50, ">"&H$5)
Does that work for you?

Glad it worked.

@Ogy Ramly

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.