Find the count of dates whose bg colour is green , month is january , year is 2016

Hello All,

I have a set of data where in there are several dates (For eg in cell A1) and alphabets (in cell A2).

When I don’t have the alphabets in certain cells, I have the count for Dates (January 2016).


If I have the alphabet it doesnt work and I get value error 🙁


I tried this : =SUMPRODUCT((YEAR($W$7:$Z$7)=2016)*(MONTH($W$7:$Z$7)=12))


How my cells look like :

Cell A1 = 10/12/2017 (January / 2017 / Green colour)

Cell B1 = 11/12/2016 (January / 2016 / Green colour)

Cell C1 = 5/12/2016 (January / 2016 / Green colour)

Cell D1 = 8/12/2016 (January / 2016 / Yellow colour)

Cell E1 = Delivered ( some words / alphabets)


What I want : 

January/2016/Green colour

Result should be 2.


Kindly help.


Thanks in advance



By: Kriti

Leave a Reply

Your email address will not be published. Required fields are marked *