Count The Number Of Yes Entries & Complete The Month Based On A Date

[npsnps]

Hi,

Wonder if someone might be able to help me out with a formula based on the below.

I have a list of information and the answer is either yes or no, and comes with a corresponding date when the work was completed.

I would like to create a month summary that looks down the list of the Yes/No, and only counts the Yes entries and counts them in the correct month, so if there were three entries with any date in January, two yes and one no, there would be a 2 in the january summary box?

[TABLE="width: 908"]
<colgroup><col><col><col><col span="3"><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]How Many Correct?[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Correct?[/TD]
[TD]When?[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Yes[/TD]
[TD]01/01/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]No[/TD]
[TD]15/01/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Yes[/TD]
[TD]10/03/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]No[/TD]
[TD]24/07/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]No[/TD]
[TD]25/09/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]No[/TD]
[TD]26/09/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Yes[/TD]
[TD]01/12/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]No[/TD]
[TD]09/12/2017[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[njimack]

Assuming row 1 contains actual dates (e.g 01/01/18 rather than "January")...
=SUMPRODUCT(--($B$6:$B$13="Yes"),--(MONTH($C$6:$C$13)=MONTH(B1)))

 

[FormR]

Hi, if you can make you month headers a date, specifically the first of the month (you can format them as mmmm if you wish), then you can try something like this:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		01/01/2017	01/02/2017	01/03/2017	01/04/2017	01/05/2017	01/06/2017	01/07/2017	01/08/2017	01/09/2017	01/10/2017	01/11/2017	01/12/2017
2	How Many Correct?	1	0	1	0	0	0	0	0	0	0	0	1
3
4		Correct?	When?
5		Yes	01/01/2017
6		No	15/01/2017
7		Yes	10/03/2017
8		No	24/07/2017
9		No	25/09/2017
10		No	26/09/2017
11		Yes	01/12/2017
12		No	09/12/2017
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIFS($B$5:$B$12,"Yes",$C$5:$C$12,">="&B1,$C$5:$C$12,"<"&EOMONTH(B1,0)+1)

 

[npsnps]

Works a treat, thank you very much!