Counting coloured cells that are blank

smilinglilies

New Member
Joined
Jan 25, 2019
Messages
9
Hi

I have a spreadsheet that has different colours in. I need to be able to count all blank orange cells within a certain range.

Countblank doesn't work, so I thought of perhaps using countccolor as I can get it to count all the orange cells but then I don't know what to add to it to count just blank cells.

Could someone point me in the right direction please

Thank you
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Can you supply the code you have for countcolor
 

smilinglilies

New Member
Joined
Jan 25, 2019
Messages
9
Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function



Using the formula =countccolor(E14:AI17,G2) gives me 23 which is correct, I now need to know how many of these 23 orange cells have no data in them.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
OK, try
Code:
Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
   If datax.Interior.ColorIndex = xcolor And datax.Value = "" Then
      CountCcolor = CountCcolor + 1
   End If
Next datax
End Function
 

smilinglilies

New Member
Joined
Jan 25, 2019
Messages
9

ADVERTISEMENT

Thank you - would I be able to use the old formula on the same spreadsheet at the same time as I use it to work out the availability overall - the new formula is to break it down into 2 and 3 bed villas and what is still available.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
You can use
Code:
Function CountCcolor(range_data As Range, criteria As Range, Optional ValU As String = "*") As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
   If datax.Interior.ColorIndex = xcolor And datax.Value Like ValU Then
      CountCcolor = CountCcolor + 1
   End If
Next datax
End Function
Then this
=countccolor(E14:AI17,G2)
will count all, or this
=countccolor(E14:AI17,G2,"")
will count blanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top