Help sum range of cells by background color WITHOUT using MACROS in Excel 2013

[Tenchi]

Hello All,

I'm new to the forum but desperate for help. I'm basically creating/forecasting shift schedules in Excel 2013. Unfortunately, my work does not allow for the use of macros. I have already built an schedule in which each cell represents a day and contains general number of hours for that day, and can easily manipulate it with Excel 2013's built-in sum formulas. To my frustrations, management wants an "easy on the eyes / color-coded" schedule that can be quickly and easily updated. Here is a link to my spreadsheet to be given to management - https://drive.google.com/file/d/0B_mJH6r7rpTkSUNiTTlBLVVzREU/view?usp=sharing.

Hiding ' 1 ' (colored to the highlighting) in each cell is too time consuming/complicated as schedules changes too frequently to meet environment needs. I basically need help building a formula that will scan a employee's shift for the 24hr period and count/sum only the cells highlighted. I've been researching this for 2 months now and no luck. Any assistance would be greatly appreciated.

 

[Dryver14]

this is not possible without VBA, you do not need to run a macro everytime though a custom function could be created but there are no standard functions

 

[Taul]

Hi,
An option is to use a number 1 in each of the cells and then use conditional format to colour the cells if they contain the value 1
If you make the text colour the same as the bar fill colour the numbers will not be seen.
Then you can use a simple sum formula in column AA

The conditonal format rule should be :-
Format only cells that contain
Cell Value
equal to
="1"

Hope that helps a little
Paul.

 

[Taul]

Hi
Correction:-

Make sure your cells are formatted as "General"
The use this Conditional Format
Format only cells that contain
Cell Value
equal to
=1