count occurences of a phrase, taking into account cell colour ?

[pudle]

hi,

im having a little trouble trying to write a formula to count the occurrences of a phrase in the range B:B, but i want to count the phrases separately depending on there cells fill colour..

ie. if the cells fill colour is red it would be counted and added to the f1 cell, if blue f2, if fill green f3

to count the phrases i was planning to use

=COUNTIF(B:B, "sam")

but this dosnt take into acount the cells colour, and i would also like to take into account variants of the phrase such as " sam" or "sam,"

is this possible using formulas or could it be writen as a macro ?

 

[T. Valko]

hi,

im having a little trouble trying to write a formula to count the occurrences of a phrase in the range B:B, but i want to count the phrases separately depending on there cells fill colour..

ie. if the cells fill colour is red it would be counted and added to the f1 cell, if blue f2, if fill green f3

to count the phrases i was planning to use

=COUNTIF(B:B, "sam")

but this dosnt take into acount the cells colour, and i would also like to take into account variants of the phrase such as " sam" or "sam,"

is this possible using formulas or could it be writen as a macro ?

You will need to use a VBA function to do this unless we can use the logic as to why a cell is a certain color. Are the cells colored using conditional formatting? If so, what are the rules used to color the cells?

 

[pudle]

the cells are colored originally using a macro thats run on other sheets much earlier in the process, all this data is then pulled in a results workbook which i were i want to count the phrases

 

[T. Valko]

the cells are colored originally using a macro thats run on other sheets much earlier in the process, all this data is then pulled in a results workbook which i were i want to count the phrases

Ok, you'll need some VBA to do that.

See these links:

http://www.cpearson.com/Excel/colors.aspx

http://xldynamic.com/source/xld.ColourCounter.html