There is a little more. The worksheet is two pages. The area that is
to change
color and is summed is made up of 4 cells. The cells are combined and
the data, ( .01 ab or 6 cd, etc) is input into the cell. It is those
4 cells that need to change color, depending on the input, as well as
to be summed separately. There are 365 groups of these 4 combined
cells.

I don't suppose one cell versus a combined group of cells may make a
hugh difference?

Thanks,

Jim

On Tue, 29 Aug 2006 03:20:02 -0700, paul
wrote:

there is a way but its hard to describe but i will try
I have the column of alphanumeric data in col f
so say cell f14 is your alphanumeric character
cell G14=VALUE(LEFT(F14,FIND(" ",F14)-1)) this finds the space and seperates
the number from the alpha part
in H 14 i have this formula
=IF(NOT(ISERROR(FIND("ab",F14,1))),"ab"
,IF(NOT(ISERROR(FIND("cd",F14,1))),"cd"
,IF(NOT(ISERROR(FIND("ef",F14,1))),"ef"
,IF(NOT(ISERROR(FIND("ghij",F14,1))),"ghif","kl") )))
this examines the alpha numeric and splits out the alpha
conditional format cell ,formula is =H14="ab", choose the format you want
condition two formula is =H14<"ab",choose the format you want.
at the bottom of the G column type these formula
for ab =SUMIF($H$14:$H$22,"AB",$G$14:$G$22)
for cd =SUMIF($H$14:$H$22,"cd",$G$14:$G$22)
for ef =SUMIF($H$14:$H$22,"ef",$G$14:$G$22)
for ghij =SUMIF($H$14:$H$22,"ghij",$G$14:$G$22)
for kl =SUMIF($H$14:$H$22,"kl",$G$14:$G$22)
i have a sample sheet that i can email you.Email address below
make sure you put something about excelforum and alphanumeric in the subject
otherwise it will go straight to the trash
There may be a more elegant way but it works!