Countif and countcolor

[smilinglilies]

Hi

I am currently trying to create a formula to count the number of cells of a certain colour that is a certain criteria.

I'm looking for something like a countcolorif function but can't work it out and I'm getting frustrated that I can't work this out!

Basic version below of spreadsheet. (Cells are coloured instead of typed in)

How do I write a formula that will count the number of orange cells in the rows that have a 2 in the first column?

[TABLE="width: 500"]
<tbody>[TR]
[TD]No Bedrooms
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]orange[/TD]
[TD]grey[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD]orange[/TD]
[TD]grey[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]grey[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[/TR]
</tbody>[/TABLE]

I know I can do this by countcolour () + countcolour() but I was wondering if there is a different and not as long winded way of doing it?

Any help and advice is greatly appreciated.

 

[Ramballah]

Hi

I am currently trying to create a formula to count the number of cells of a certain colour that is a certain criteria.

I'm looking for something like a countcolorif function but can't work it out and I'm getting frustrated that I can't work this out!

Basic version below of spreadsheet. (Cells are coloured instead of typed in)

How do I write a formula that will count the number of orange cells in the rows that have a 2 in the first column?

[TABLE="width: 500"]
<tbody>[TR]
[TD]No Bedrooms[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]orange[/TD]
[TD]grey[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD]orange[/TD]
[TD]grey[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]grey[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[/TR]
</tbody>[/TABLE]

I know I can do this by countcolour () + countcolour() but I was wondering if there is a different and not as long winded way of doing it?

Any help and advice is greatly appreciated.

dunno if this helps but
https://support.microsoft.com/nl-nl...f-cells-with-specific-cell-color-by-using-vba

 

[smilinglilies]

Thank you - I have used VBA to create the countcolor function - it's tying the countif and countcolour together that I am now struggling with.

 

[My Aswer Is This]

If you want a vba solution we need a lot more details.

Where on the sheet are your two ranges

Show us the script you have now.

 

[Ramballah]

If you want a vba solution we need a lot more details.

Where on the sheet are your two ranges

Show us the script you have now.

Thats not needed, i just understood his question just now,
Here use this
=if(A1=2,CountColour(C1:F1,B1),"anyanswer u want"))
Basically just change wat u need to change, u get the concept i hope
im so bad at typing cell references lmao

 

[smilinglilies]

The VBA I have now for the countcolor is:

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

My criteria is currently is D12:D43 (number of bedrooms) and then E12:AI43 (coloured cells to show what is available)

 

[Ramballah]

So for every color you need 1 cell that has that color so you can make it absolute.
For example, A1 u got the color blue so in ur formula when u need it to count the blue's u take $A$1 As reference.
Give me the cell which you want to take reference for the colours, then i can give you the formula u need for every row. Also ur countcolour is it =Countcolour(range, criteria) or is it countccolor like mine?

 

[smilinglilies]

Honestly - the majority of excel is a different language to me but I think following on from your earlier post I need something like =IF(A12:A43=2,countccolor(E14:AI43,B3)) but that doesn't work when I tried it.

 

[Ramballah]

Honestly - the majority of excel is a different language to me but I think following on from your earlier post I need something like =IF(A12:A43=2,countccolor(E14:AI43,B3)) but that doesn't work when I tried it.

Thats cos u got IF(A12:A43
u cant take a range for if without it getting messy :P. thats why i said per row u can do this formula and make a sum for a total.
so u will get something like this

Color: red
Room nr
2 formula here red red blue blue pink
2 formula here yellow orange red
3 formula here red blue black
3 formula here yellow orange red
sum

 

[smilinglilies]

Ah right - so it looks like it does need to be the long winded version, I just thought that there might be a quicker way to put it all together.

Thank you for your help