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?

January

February

March

April

May

June

July

August

September

October

November

December

How Many Correct?

1

0

1

0

0

0

0

0

0

0

0

1

Correct?

When?

Yes

01/01/2017

No

15/01/2017

Yes

10/03/2017

No

24/07/2017

No

25/09/2017

No

26/09/2017

Yes

01/12/2017

No

09/12/2017

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

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!