How to calculate sums according to colours?

You can't without VBA. I am moving the thread accordingly.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)



Then use this formula, as shown in the sheet:
=SumCellsByFontColor($B2:$F2,G$1)

copied across and down. Format cells as [h]:mm. If you change the shadings, recalculate (Fn F9 on my laptop...)

Then what you are asking for is impossible.... unless there are rules that govern which cells are coloured. If there are conditional formatting rules in use, the the same rule can be used for summing.

So, how are the font colours assigned?

Can you use XLM functions rather than VBA?

Here is about the only way I know of, if you aren't using CF as Glenn mentioned earlier. It uses a defined name (CellColours) using the GET.CELL xlm function
=GET.CELL(24,INDIRECT("Sheet1!RC",0))+NOW()*0
in a mirror table on another sheet to return the font colour of each cell, and those are then used in the formulas. I borrowed Glenn's sample workbook for the layout.

And what makes a value high... and what makes a value low??

The formulae should have been sumif

=SUMIF(Sheet2!$B2:$F2,3,$B2:$F2)

and

=SUMIF(Sheet2!$B2:$F2,23,$B2:$F2).

You still have to fn F9, or equivalent, to refresh and you still have to save as a macro-enabled file. However, if you describes the RULES that underlie the whole thing... What makes this value high and that value low??? then it should be completely do-able.

Quite agree. Colour should be used to highlight value, not as a value.

Originally Posted by Nickitta

Hello

I should be able to work with simple macros.

Thanks

To use macros, you need a macro-enabled workbook - maybe that is not allowed at work, either?

It would solve all the problems if you added a row above each day where you could flag the intensity for each cell. You could then use straight SUMIF formulas for your totals, and use CF if you still need the cells coloured.

It strikes me that you are doing your best to make life REALLY complicated for yourself. So... you select a few cells, randomly (???), change their font colour and want the totals to be summed by colour without using macros. That can not be done.

xlnitwit's suggestion, above, is your best chance of getting something workable.

I am NOT making my life complicated, I've inherited that spreadsheet. The cells are not selected randomly. As explained previously Red means high and blue low?

Well, I have provided you with what I believe to be about the only option to work with the layout you have, without VBA. However, I would strongly suggest that you change the layout if at all possible. It will make everything more robust and easier to report on.

Originally Posted by Nickitta

I think I need to ask the person in charge to rethink this Excel sheet.

I think that's a good idea! If they disagree, show them the alternative.

The point is HOW do you know WHICH cells need to be coloured red and which need to be coloured blue?