• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif (For a specific colour + a specific year + Specific month)

Kriti

New Member
Hello All,
I would like to count for red colour ,Jan, 2016 ( FOR EXAMPLE).
Tried using sumproduct (worked only for Jan, 2016). didnt work for the colour.

=SUMPRODUCT((YEAR(RANGE)=2016)*(MONTH(RANGE)=12))

Please find the attached sample for better understanding.

Note : I'm not using conditional formatting in this case :)

Would like to have solutions for with and without conditional formatting !

Need help !
Thanks,
Kiki
 

Attachments

  • sample.xlsm
    11.3 KB · Views: 11
You can't use cell color directly in formula.

What is the underlying condition/logic for these colors?

If there isn't definable logic via formula etc, then you'll need VBA to read the cell's interior color index.
 
Thanks for the response ..

No logic .. !

Once the issue is solved... colours are changed

Regards,
Kiki
 
Hi ,

There are old Excel 4 Macro functions which allow you to get the cell interior color and other properties.

=GET.CELL(38,cell address)

will return a number corresponding to the cell interior color.

However , even this does not work in the case of conditionally formatted cells.

If your cells are all manually filled with a certain color , then the above formula will work.

You cannot enter these formulae directly in worksheet cells ; you need to create a named range to access them.

Create a named range , calling it say CellColor , and in the RefersTo box enter this formula.

However , before doing this , you need to place the cursor in a cell relative to the cell whose color you wish to access ; suppose you have the colored cells in column J , and you can enter the formula only in cell Z.

First , place the cursor in say Z1 ; then , when creating the named range , enter the following formula in the RefersTo box :

=GET.CELL(38,J1)

Now , entering the formula :

=CellColor

in cell Z17 , will return the color of the cell J17.

Narayan
 
Hi ,

Since the GET.CELL functions are called Excel 4 Macro functions , when you save your workbook , you will have to save it as a macro enabled file , not as a .xlsx file.

Narayan
 
Hi ,

There are old Excel 4 Macro functions which allow you to get the cell interior color and other properties.

=GET.CELL(38,cell address)

will return a number corresponding to the cell interior color.

However , even this does not work in the case of conditionally formatted cells.

If your cells are all manually filled with a certain color , then the above formula will work.

You cannot enter these formulae directly in worksheet cells ; you need to create a named range to access them.

Create a named range , calling it say CellColor , and in the RefersTo box enter this formula.

However , before doing this , you need to place the cursor in a cell relative to the cell whose color you wish to access ; suppose you have the colored cells in column J , and you can enter the formula only in cell Z.

First , place the cursor in say Z1 ; then , when creating the named range , enter the following formula in the RefersTo box :

=GET.CELL(38,J1)

Now , entering the formula :

=CellColor

in cell Z17 , will return the color of the cell J17.

Narayan
I am able to get the cell colour and count seperately. But, not together !
 
I cannot have the colour code in my file as shown in the file (from your link)

No no, you don't return the code, but combine concept of UDF returning array with original UDF to return array of 0s and 1s in UDF. Where it will serve as true/false flag for if criteria color is in range.

You then will need to combine that array within another formula (such as SUMPRODUCT) to obtain final result.

However, your data layout isn't ideal for performing analysis on it. I'd strongly recommend restructuring your data to flat table.
 
Hi,

upload_2017-5-4_11-50-20.png

1] Try to use "Filter by color" built-in function under "Filter"

2] Add helper Column O to Column Z

3] Filled Column B to Column D empty cells with 0

4] Changed all cells background color into front color (Red or Green)

5] Filter Column B to Column D >> check "Filter by color" >> choose Red color >> enter/fill "1" to the corresponding helper columns

6] Then, check "Filter by color" >> choose Green color >> enter/fill "2" to the corresponding helper columns

7] Entered "1" to J24, and "2" to K24 and applied custom cell format >> [=1]"Red";[=2]"Green"

8] In J26, formula copy to K26 :

=SUMPRODUCT((YEAR(N(+$B$4:$E$20))=$I$25)*(MONTH(N(+$B$4:$E$20))=MONTH($H$25&"/"&$I$25))*($O$4:$R$20=J$24))

Regards
Bosco
 

Attachments

  • CountCellByColor.xlsm
    41.6 KB · Views: 4
Last edited:
No no, you don't return the code, but combine concept of UDF returning array with original UDF to return array of 0s and 1s in UDF. Where it will serve as true/false flag for if criteria color is in range.

You then will need to combine that array within another formula (such as SUMPRODUCT) to obtain final result.

However, your data layout isn't ideal for performing analysis on it. I'd strongly recommend restructuring your data to flat table.

Ok , will check ! thanks for your help
 
Bosco,

View attachment 41361

Can you send to me a working example - would be really useful ! Thanks
Here is my attached file (post #14) formula results :

upload_2017-5-4_15-34-27.png

Maybe your Excel version do not have the "Filter by Color" option.

The "Filter by Color" option available in Excel 2010, Excel 2013 and Excel 2016.

Regards
Bosco
 
Last edited:
Is it possible to get the result without the table on the right ? will be better >>>>>>>> Thanks anyway Bosco !
 
Limitation : The changes do not automatically reflect on the tale on the right :(
upload_2017-5-4_9-41-58.png
 

Attachments

  • upload_2017-5-4_9-41-21.png
    upload_2017-5-4_9-41-21.png
    12.3 KB · Views: 3
Back
Top