count different type of blank cell in an excel file

[Rakesh123]

Hi All,
Please help.
I have a column where each cell has 4 values in a dropdown - potato,tomato,orange and a blank value. In between each cell, I have some coloured blank cell just to separate cells. This has no use but required for differentiation,

I want to count number of blank cells and number of potatoes. This I will be doing in another worksheet of the same file. I can use the countif function- count potatoes + count blanks. But the problem is the coloured blank cells present in between. Is there any other way to exclude the coloured cells from counting.
Hope my question is clear.
I don't want to use macro.

 

[Khalid NGO]

Hi Rakesh,
Can you post your question in a sample xls file with expected result?

 

[udprocks]

Hi rakesh,

i think countif is work for you,,, like

for potato

=countif(range,"potato")

and for blank

=countblank(range)

what is the issue with this?

 

[Nebu]

Fill the colored cells with "X" or some other value and change the color of the font to the same color as the cell's background color and trying counting it , since the colored cells are no longer blank it would be fairly easy...

 

[kuldeepjainesl]

Or you can try =COUNTIFS($A$1:$A$15,"="&$B$1)
Here A1:A15 is the range to be checked and B1 is the value to be counted.

 

[Gangadhar Yeligaty]

Hi Rakesh,

Please find attached solution for your problem (but you need to use a small Custom function). This will exclude the coloured cells from counting.

Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function

You can delete this function once you get the output.

I hope this helps you....

Regards,
Gangadhar Yeligaty