# Formula for counting data per month

#### Akumar28

##### New Member
Hi

I am trying to get a formula to calculate the amount of Y's and N's for one month i.e. April 18 per project and my dates vary from the beginning and ending of the month.

My data is below:

 Project Name 11 April 2018 25 April 2018 09 May 2018 23 May 2018 06 June 2018 20 June 2018 04 July 2018 18 July 2018 01 August 2018 15 August 2018 29 August 2018 12 September 2018 26 September 2018 10 October 2018 24 October 2018 07 November 2018 21 November 2018 05 December 2018 19 December 2018 Project 1 N Y N Y N N Y Y A/L Y N Y N Y Y N Project 2 Y Y Y Y Y Y Y Y Y Y N Y Y Y Y Y Project 3 N/A N N/A N N/A Y Y N/A N/A Y N/A Y N/A Y N/A N/A Project 4 Y Y Y Y Y A/L Y Y Y Y Y Y Y Y Y Y

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

##### MrExcel MVP
Try something like...

=COUNTIFS(A2:Z2,"Y",\$A\$1:\$Z\$2,">="&DATE(2018,4,1),\$A\$1:\$Z\$2,"<="&EOMONTH(DATE(2018,4,1),0))

#### oldbrewer

##### Board Regular
 Project Name 11-Apr-18 25-Apr-18 09-May-18 23-May-18 06-Jun-18 20-Jun-18 row 1 Project 1 N Y N Y N N Project 2 Y Y Y Y Y Y Project 3 N/A N N/A N N/A Y Project 4 Y Y Y Y Y A/L col F month 01/04/2018 end day 30/04/2018 N Y row 21 Project 1 1 1 Project 2 0 2 Project 3 1 0 Project 4 0 2 project 1 N=1 formula =SUMPRODUCT((\$B\$1:\$G\$1>=\$F\$16)*(\$B\$1:\$G\$1<=\$F\$17)*(B2:G2=E\$21))

<colgroup><col><col span="2"><col><col><col><col span="8"></colgroup><tbody>
</tbody>

#### Akumar28

##### New Member
Thanks Aladin I have found your formula to be most useful and I will now just edit the dates for the other months I require. Many thanks !

#### Akumar28

##### New Member
With the help of the Forum I have now managed to work out a formula to use. Many Thanks to all who helped.

##### MrExcel MVP
Thanks Aladin I have found your formula to be most useful and I will now just edit the dates for the other months I require. Many thanks !
You are welcome.