Trying to look up Text match in a cell and display count of other range

[HostaGeek]

Hi There,


I am trying to look up a column which has a date EG 5 November 2012 and I am trying to search for all the November's in the range and where it matches to count the Yes or No in another column range and display the count - Can anyone help ?


Host A geek

 

[Debraj]

Hi Geekhoster,


Can you please try the below for me./

=SUM(IF(MONTH($A$2:$A$20)=11,1,0))


Confirm the formula by pressing [b]Ctrl + Shift + Enter, Not Just Enter..


Adjust the range as per requirement.. Hope it will works.. in any version of excel and in any language also.. :)


EDIT:


I just read the heading of the post.. It consist TEXT word..

So please give one more try..


=SUM(IF(ISNUMBER(SEARCH("Nov",A2:A20)),1,0))

again with Ctrl + Shift + Enter[/b]


Regards,

Deb

 

[NARAYANK991]

Hi ,


If you can use the COUNTIFS function , you can try this :


=COUNTIFS(D5:D17,">="&DATEVALUE("11/1/2012"),D5:D17,"<="&DATEVALUE("11/30/2012"),G5:G17,"NO")&" NOs and "&COUNTIFS(D5:D17,">="&DATEVALUE("11/1/2012"),D5:D17,"<="&DATEVALUE("11/30/2012"),G5:G17,"YES")&" YESs"


I have assumed the following :


D5:D17 is the range of dates ( in my case the date format is mm/dd/yyyy )


G5:G17 is the range having text values NO and YES.


Narayan