Count coloured cells - But treat cells with colour and value as 0.5

**Wabby** · 10-19-2009, 11:04 AM

Hi guys,

I have a spreadsheet and a module that allows me to count coloured cells.

If the cell with a colour in has ANY text in it, then I need it to count it as 0.5 instead of 1.

Any ideas?

Current Code:

Please Login or Register  to view this content.

Cheers

**DonkeyOte** · 10-19-2009, 11:33 AM

Using your existing function change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If you range contains formulae nulls and you wish for them to be excluded then

Please Login or Register  to view this content.

**Wabby** · 10-19-2009, 11:57 AM

If it has a value (text value) can I make it count is as 0.5 instead of 1?

Thanks for the help so far

**DonkeyOte** · 10-19-2009, 12:08 PM

Let's just clarify what you want to do exactly.

For counting cells of a given colour, non-blanks are worth 0.5 and blanks 0 ?

For summing cells of a given colour, non-numerics worth 0

**DonkeyOte** · 10-19-2009, 12:15 PM

I'm heading off line, assuming prior post is correct then below is the original condensed and altered.

Please Login or Register  to view this content.

Though FWIW IMO it is ill advised to differentiate / calculate based on "feel / appearance".

**Wabby** · 10-19-2009, 12:24 PM

Originally Posted by DonkeyOte

Let's just clarify what you want to do exactly.

For counting cells of a given colour, non-blanks are worth 0.5 and blanks 0 ?

For summing cells of a given colour, non-numerics worth 0

Non blanks are worth 0.5 and blanks are worth 1.

I am trying to count a spreadsheet that has been in use for several years - usings colours to display holiday days - And text within that colour to stipulate half a holiday day.

Many thanks for your help so far

**DonkeyOte** · 10-19-2009, 12:26 PM

Non blanks are worth 0.5 and blanks are worth 1

Change the 0 in the IIF to 1.

usings colours to display holiday days - And text within that colour to stipulate half a holiday day

Would be better to use H (or 1) in cell to denote Holiday and HH (or 0.5) for Half Holiday - use Conditional Formatting to apply colour based on the cell value if required. No VBA required from that point forth.