Counting color cells that has value of less or equal to 2

Hi, i have a table that looks like this in the attached file. The example only shows 4 columns by in my real working file, there's 36 columns.

Basically, i need help to count the number of colored cells (any colors) that has value of less or equal to 2 by columns. Is there any way to do it?

I found some solutions on internet by counting colored cells based on color index. But that is not what i need. I do not care about color index. As long as i can count any colored cells within same column that is <=2.

Thank you.


Capture.JPG

How are the cells actually colored?
Are they conditional formatting? If so, you can base your count on those conditions.

Are they standard manual formatting? That will be difficult, to say the least.
Is there any pattern or reason to indicate why the cells are colored ?
Or a reason others are NOT colored ?

Hi Janmo1

The cells are colored manually based on product type. Product 1 = LIght blue, Product 2= dark blue etc

Hi JohnTopley
=COUNTIF(B:B,"<=2") only counts rows that is less or equal to 2 without consideration of colored cells. But i need to only count colored cells that is less or = to 2

So there isn't any (formula-based) selection criteria to identify whether a cell is coloured?

Hi Janmo1

The cells are colored manually based on product type. Product 1 = LIght blue, Product 2= dark blue etc

Great, so you should be able to do
=COUNTISF(A1:A100,"<=2",B1:B100,"Product1")

For multiple products, do
=SUM(COUNTIFS(A1:A100,"<=2",B1:B100,{"Product1","Product2","Product3"}))

thanks. But both formula returns #Value error

Post a sample file.

To upload file clicj "Go Advanced" then scroll down to "Manage Attachments"

Hi,

Attached is the sample file.

Every week, when supplier sent over the SKU stock count list. I'll update in the inventory qty column by SKU and by Store and manually colored the rows.

Then i need to count the number of colored cells that is less than or equal to 2.

Thanks
Rac

You can do that using a helper column with the help of Get.Cell

See the attachment. Press Ctrl+F3 to see the formula of named range color.

Thank you so much. It works perfectly! (even though right now i have 35 columns and will need to create another set of 35 columns more to pickup the color index).

Originally Posted by yankiessg

Thank you so much. It works perfectly! (even though right now i have 35 columns and will need to create another set of 35 columns more to pickup the color index).

I wish if anyone else can give you any other solution to this, so that you don't need to create more columns.

Here's a VBA solution.

Put this in a standard module

Please Login or Register  to view this content.

You can use it like this to get a true or false result if the cell has a fill color
=HasColor(A1)

It has an optional 2nd argument to make it check for a specific color.
=HasColor(A1,B1)
Where B1 would be another cell that is the specific color you want to test for.

Then to use it to make a count, with criteria <=2
=SUMPRODUCT(--HasColor(B3:B27),--(B3:B27<=2))