Formula for counting data per month

Akumar28

New Member
Joined
Nov 20, 2018
Messages
3
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 Name11 April 201825 April 201809 May 201823 May 201806 June 201820 June 201804 July 201818 July 201801 August 201815 August 201829 August 201812 September 201826 September 201810 October 201824 October 201807 November 201821 November 201805 December 201819 December 2018
Project 1NYNYNNYYA/LYNYNYYN
Project 2YYYYYYYYYYNYYYYY
Project 3N/ANN/ANN/AYYN/AN/AYN/AYN/AYN/AN/A
Project 4YYYYYA/LYYYYYYYYYY

<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>
 

Some videos you may like

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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
Joined
Apr 11, 2010
Messages
10,993
Project Name11-Apr-1825-Apr-1809-May-1823-May-1806-Jun-1820-Jun-18row 1
Project 1NYNYNN
Project 2YYYYYY
Project 3N/ANN/ANN/AY
Project 4YYYYYA/L
col F
month01/04/2018
end day30/04/2018
NYrow 21
Project 111
Project 202
Project 310
Project 402
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>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Project Name</td><td style="font-weight: bold;text-align: right;;">11-Apr-18</td><td style="font-weight: bold;text-align: right;;">25-Apr-18</td><td style="font-weight: bold;text-align: right;;">09-May-18</td><td style="font-weight: bold;text-align: right;;">23-May-18</td><td style="font-weight: bold;text-align: right;;">06-Jun-18</td><td style="font-weight: bold;text-align: right;;">20-Jun-18</td><td style="font-weight: bold;text-align: right;;">04-Jul-18</td><td style="font-weight: bold;text-align: right;;">18-Jul-18</td><td style="font-weight: bold;text-align: right;;">01-Aug-18</td><td style="font-weight: bold;text-align: right;;">15-Aug-18</td><td style="font-weight: bold;text-align: right;;">29-Aug-18</td><td style="font-weight: bold;text-align: right;;">12-Sep-18</td><td style="font-weight: bold;text-align: right;;">26-Sep-18</td><td style="font-weight: bold;text-align: right;;">10-Oct-18</td><td style="font-weight: bold;text-align: right;;">24-Oct-18</td><td style="font-weight: bold;text-align: right;;">07-Nov-18</td><td style="font-weight: bold;text-align: right;;">21-Nov-18</td><td style="font-weight: bold;text-align: right;;">05-Dec-18</td><td style="font-weight: bold;text-align: right;;">19-Dec-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Project 1</td><td style=";">N</td><td style=";">Y</td><td style=";">N</td><td style=";">Y</td><td style=";">N</td><td style=";">N</td><td style=";">Y</td><td style=";">Y</td><td style=";">A/L</td><td style=";">Y</td><td style=";">N</td><td style=";">Y</td><td style=";">N</td><td style=";">Y</td><td style=";">Y</td><td style=";">N</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Project 2</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">N</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Project 3</td><td style=";">N/A</td><td style=";">N</td><td style=";">N/A</td><td style=";">N</td><td style=";">N/A</td><td style=";">Y</td><td style=";">Y</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">Y</td><td style=";">N/A</td><td style=";">Y</td><td style=";">N/A</td><td style=";">Y</td><td style=";">N/A</td><td style=";">N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Project 4</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">A/L</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/04/2018</td><td style="text-align: right;;">01/04/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">Y</td><td style=";">N</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">PFB 003</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">$B$1:$T$1</font>)=MONTH(<font color="Green">B$8</font>)</font>)*(<font color="Red">$B$2:$T$5=B$9</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C10</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">$B$1:$T$1</font>)=MONTH(<font color="Green">C$8</font>)</font>)*(<font color="Red">$B$2:$T$5=C$9</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Akumar28

New Member
Joined
Nov 20, 2018
Messages
3
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
Joined
Nov 20, 2018
Messages
3
With the help of the Forum I have now managed to work out a formula to use. Many Thanks to all who helped.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,484
Messages
5,444,747
Members
405,299
Latest member
rcurtin

This Week's Hot Topics

Top