Count and provide value of colored cells in a row

[djaurit]

Suppose I have a file with many rows of data, some cells are colored (flagged with an error), in several columns C, D, E, F, & G. Not all cells are colored in each row. However I would like to add a column at the end that captures a count if a cell in the row is colored.

Referring to the attached example... I would expect a value of 1 in G3, 1 in H4, 1 in F6, 1 in H7, 1 in D8 and E8.

Appreciate all and any help! Thanks!

[daffodil11]

There isn't a default function that can do this, but it can be done with a UDF.

To add this to your worksheet, in Excel hit Alt+F11, Insert -> Module, and paste this code in there.

Please Login or Register  to view this content.

This gives you a new function called ColorFunction. The parameters are =COLORFUNCTION(a cell with the color you're looking for, the range, True for SUM or False for Count)

So, in your workbook in F2 I put:

=ColorFunction($C$6,[@[Job Name]],FALSE) since C6 is orange, and I want a count of the cells not the sum of their values.

Then I just dragged that formula down and over.

[djaurit]

Thank you daffodil11..... Works like a charm. Correct me if I'm wrong but it appears that if I modify a cell (flag another after applied function)... I need to reapply the function in order to see the correct counts - right? Or did I miss something; is there a way to automate the update?

Thanks again -- very helpful!

[Kashus]

There isn't a default function that can do this, but it can be done with a UDF.

To add this to your worksheet, in Excel hit Alt+F11, Insert -> Module, and paste this code in there.

Please Login or Register  to view this content.

This gives you a new function called ColorFunction. The parameters are =COLORFUNCTION(a cell with the color you're looking for, the range, True for SUM or False for Count)

So, in your workbook in F2 I put:

=ColorFunction($C$6,[@[Job Name]],FALSE) since C6 is orange, and I want a count of the cells not the sum of their values.

Then I just dragged that formula down and over.

I am not gonna lie, Daffodil11, that is more than a little awesome. Gonna use this as well.