1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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)

Discussion in 'Ask an Excel Question' started by Kriti, May 2, 2017.

  1. Kriti

    Kriti New Member

    Messages:
    16
    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

    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    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.
  3. Kriti

    Kriti New Member

    Messages:
    16
    Thanks for the response ..

    No logic .. !

    Once the issue is solved... colours are changed

    Regards,
    Kiki
  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    Thomas Kuriakose likes this.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,669
    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
    Thomas Kuriakose likes this.
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,669
    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
  7. Kriti

    Kriti New Member

    Messages:
    16
  8. Kriti

    Kriti New Member

    Messages:
    16
    I am able to get the cell colour and count seperately. But, not together !
  9. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    Thomas Kuriakose likes this.
  10. Kriti

    Kriti New Member

    Messages:
    16
    Ok ! I shall try and get back to you in few days ! thanks for your help chihiro !
  11. Kriti

    Kriti New Member

    Messages:
    16
    I cannot have the colour code in my file as shown in the file (from your link)
  12. Kriti

    Kriti New Member

    Messages:
    16
    If you see my sample - you will understand clearly - what my requirement is !
  13. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    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.
  14. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    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

    Attached Files:

    Last edited: May 3, 2017
    Thomas Kuriakose likes this.
  15. Kriti

    Kriti New Member

    Messages:
    16
    Ok , will check ! thanks for your help
  16. Kriti

    Kriti New Member

    Messages:
    16
    Bosco,

    upload_2017-5-3_9-14-49.png

    Can you send to me a working example - would be really useful ! Thanks
  17. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    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: May 3, 2017
  18. Kriti

    Kriti New Member

    Messages:
    16
    Coool ! wiLL CHECK
  19. Kriti

    Kriti New Member

    Messages:
    16
    Thanks ! will update if it works for me
  20. Kriti

    Kriti New Member

    Messages:
    16
    Is it possible to get the result without the table on the right ? will be better >>>>>>>> Thanks anyway Bosco !
  21. Kriti

    Kriti New Member

    Messages:
    16
    Limitation : The changes do not automatically reflect on the tale on the right :(
    upload_2017-5-4_9-41-58.png

    Attached Files:

  22. Kriti

    Kriti New Member

    Messages:
    16
    Ad why does it stop working all the time?
    upload_2017-5-4_9-45-32.png
  23. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    Upload your file for us to check the problem.

    Regards
    Bosco
  24. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    You need to check "Filter by color" >> choose Red color >> By hand fill "1" to the corresponding helper columns in right.

    Regards
    Bosco
  25. Kriti

    Kriti New Member

    Messages:
    16
    The whole point is to mAke it automatic? Bosco !

Share This Page