May i sugest using a User Defined Function, to get cell or font color, as in example attached, then use this new functions in your formulas.

Attached Files

example.xlsm (18.1 KB, 10 views)

Thank you for your response and the example, however i'm not amazingly technical with Excel and the forumla's have confused me a little bit. Could you perhaps explain what you have done so I could adapt it to my spreadsheet?

Thanks in advance,
Dr4ke

in the new attached example, in columns K and L, i used the UDF to get the cell color.
=IFERROR(IF(SEARCH("Person 1";C6)>0;SUMIFBYCOLOR(D6:E6;3;D6:E6;FALSE);0);0)-this formula in column G searches first cell for "Person 1" and sums the green range if the cell colour is 3 (means red to excel) all results are totalised in cell G6 for person 1.
Same for person 2 in column H...

This formula in cell J2:
=COUNTIFS(C$6:C$20;C$6;L$6:L$20;"=3")+COUNTIFS(C$6:C$20;C$6;K$6:K$20;"=3")
counts how many cells are red (=3) for C$6 (person 1), (depending to ranges L$6:L$20 and K$6:K$20)

This formula in cell J3:
=COUNTIFS(C$6:C$20;C$7;L$6:L$20;"=3")+COUNTIFS(C$6:C$20;C$7;K$6:K$20;"=3")
counts how many cells are red (=3) for C$7 (person 2), (depending to ranges L$6:L$20 and K$6:K$20). In the example, the result is 0, because there are no red cells for person 2...
but if you change =3 to =43, in J3 formula, you will get the number of cells that are color 43 for person 2... and so on, ....

This formula will get the cell color:
=CELLCOLORINDEX(D6;FALSE)
This formula will get the font color used in that cell:
=CELLCOLORINDEX(D6;TRUE)

NOTE: When you change the background or font color of a cell, Excel does not consider this to be changing the value of the cell. Therefore, it will not recalculate the worksheet, nor will it trigger a Worksheet_Change event procedure. This means that the values returned by these functions may not be correct immediately after you change the color of a cell. They will not return an updated value until you recalculate the worksheet by pressing ALT+F9 or by changing the actual value of a cell.
NOTE: These functions will not detect colors that are applied by Conditional Formatting.


If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins...). Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet.

Attached Files

example.xlsm (19.0 KB, 13 views)

Hi Catalin
I have looked at this solution using Excel 2010 in order to build on my own knowledge. However as soon as I enable the worksheet all the formulae fail to work and I get #NAME errors in Columns J, K and L, and 00:00:00 in Columns G and H.
Presumably this is because the Function VBA code is not contained in the spreadsheet sample you provided?
Please advise.
Thanks
Tony(OTPM)