Add up formated numbers

[turks393]

If in one cell you had blue number and red numbers is there any way you could total all the blue numbers in one cell and all the red numbers in a different cell? for example: John stopped for 0.5 hrs (blue), Bill ran for 0.8 hrs (red), Jill stopped for 1.5 hrs (blue), Joe was here for 8.5 hrs (blue). Can we have two cells one that totals the red numbers (0.8) and one for the blue numbers (10.5)?

Thanks!

 

[mintjulep]

What makes the numbers red or blue?

 

[turks393]

The user highlighs it and changes the color.

 

[zahead31]

I would suggest using a different column as a sum condition. For example:

Col A: Name
Col B: Stop/Run
Col C: # of Hours

The hours in column C can be colored blue or red by conditional formatting based on whether there is a 'Stop' or 'Run' in column B. Likewise, you can use SUMIF to total the 'Stop' hours and total the 'Run' hours separately.

Otherwise, I believe you would need to use VBA to write a user defined function.

Hope this helps,
Matt

 

[AnotherHiggins]

turks393:

You should definitely store your data properly, as zahead31 described. You could create drop-downs in column B to let users select "run" or "stop".

The only way to total via color is with VBA (a macro). If you want to explore such a solution, please post a new thread in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mintjulep]

The user selects the color based on his or her whim?