[mal graham]

I have created an excel 07 spreedsheet:- cells is split into 15 min startting at 00:00 (@B2) to 23:34 @CS2)

in a3 I have driving time
in a4 other work
in a5 POA
in a6 break

each cell I have managed to create a different colour when u click on the relevant cells (row 3 - red, row 4 - blue, row 5 - green & row 6 - grey) this was done by placing a code on the tab sheet.

I'm now stuck as I want to be able to count each row by the relevant number of cells coloured (ie CB3:CE3 +CI3:CL3) = 8 cells but I want this to be calculated as each coloured cells represents 15 minutes.

this would need to be for each of the above rows A3:A6.

Any assistance is appreciated

Malcolm

[JBeaucaire]

This isn't built into Excel. You will need to add some new User Defined Functions to your sheet to be able to count cells by colorindex.

Here's some suggested sources with ready-to-try UDFs you can look into:

Sum/Count Cells By Fill Or Background Color in Excel
Color Functions In Excel
Count by color using VBA in Microsoft Excel

The 3rd one may the most quick to try, though not necessarily the most robust.

Good luck, counting by color is an inherently "human" thing to do, and doing it with formulas in Excel is (in my opinion) a crazy manual way to do things.

What I mean is... the only way for you to be able to count by colors is if you first manually go through and start coloring cells. Holy cow that's a manual process. I would step back and see if there's any way to automate what I'm manually doing in terms of the coloring. If I can teach Excel to color the cells FOR me, then the same macros could do the counting the same way, or at least at the same time.

Anyway, color-counting... ugh... good luck.