Find number of filled dates if match to perticular cell

[bhavitparikh2001]

Hi Folks,
This might be easy for you but I am sick looking on Google. I have three columns (BF,BG&BH) with dates and column A has various texts that I would like to match with. I run this report everydaya dn spend lot of time to fill tracker manually. I would like to know if from BF (as 3-5 years of dates) filter date to current year and month then look for value in column A (i.e. Radio) and give me number.

A BF BG BH
Radio 12/03/2013 13/03/2013 10/03/2013
Radio 12/04/2013 12/03/2013 10/03/2013

I am after formula to find number of dates in each program.

thanks

 

[Akashwani]

Hi and welcome to MrExcel.

The following is Sample Data and Example results, does this show what you are wanting to achieve?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Text	Date1	Date2	Date3	Filter1	Filter2		Date	Text	Count
2	Radio	01/04/2013	02/04/2013	03/04/2013	3	1		17/04/2013	Radio	9
3	Tv	01/04/2013	02/04/2013	03/04/2013	0	0
4	Radio	10/04/2013	11/04/2013	12/04/2013	3	1
5	Tv	10/04/2013	11/04/2013	12/04/2013	0	0
6	Tv	01/03/2013	02/03/2013	03/03/2013	0	0
7	Tv	01/04/2012	02/04/2012	03/04/2012	0	0
8	Radio	01/04/2012	02/04/2012	03/04/2012	0	0
9	Radio	15/04/2013	16/04/2013	17/04/2013	3	1
10	Radio	15/04/2012	16/04/2012	17/04/2012	0	0
11
Sheet14

The formulas in B2:C2 need to be copied down.
You can then filter your results based on Column B or Column C to view the matches.
Or you can use a small table H1:J2 to get the count of the matches.
Or you could just use this formula to get the count....

=SUMPRODUCT(--(YEAR($B$2:$D$10)=YEAR(TODAY())*(MONTH($B$2:$D$10)=MONTH(TODAY()))*($A$2:$A$10="Radio")))

If any of the above does work for you, then you will need to change the cell references to suit your layout.

Ak