Extending a formula to recognize an additional field regarding dates and months

[searchingforhelp]

Hi all,

I am trying to manipulate the formula by counting how many "YES" are in a particular month.

Calendar Days.xlsx
	A	B	C	D
1	Date	Category	Month	Total by Month
2	5/10/2021	Yes	Jan-21	0
3	7/3/2021	Yes	Feb-21	0
4	9/21/2021	Yes	Mar-21	0
5	12/12/2021	No	Apr-21	1
6	4/15/2021	No	May-21	4
7	5/20/2021	Yes	Jun-21	0
8	5/25/2021	Yes	Jul-21	2
9	12/1/2021	No	Aug-21	0
10	5/6/2021	No	Sep-21	1
11	7/15/2021	No	Oct-21	0
Sheet5

Cell Formulas
Range	Formula
D2:D11	D2	=COUNTIFS(A:A,">="&C2,A:A,"<="&EOMONTH(C2,0))

 

[searchingforhelp]

Thank you Joe for your time. Unfortunately, the formula is not working properly. For some reason if I delete the data and copy and paste new data in the same fields, the formula is not adjusting. It stays blank. In the below example I deleted the data that was originally in sheet1 and randomly place four dates in row 7, 12,15 and row 16. The formula should have detected the dates with the "yes" and outlined the results in sheet2. However, in sheet2 May-21 and Oct-21 populated with zero which should not be the case as there is no May and October with the "yes" listed.

I am wondering if the formula is locating the rows in Sheet2 and matching it with the rows is Sheet1, instead of identifying the dates with the "yes" in the entire A & B column within sheet1. I came up with this theory because I noticed in sheet2 a 0 number populated from the data located in row 7 and 12 in sheet1 which is incorrect results. I also noticed how row 15 and 16 in sheet1 was not detected which should have been recognized and counted.

Not sure how much more of your time you can allocate. I am thankful for all the help thus far.

Calendar Days.xlsx
	A	B	C	D	E	F
1	Sheet 1			Sheet 2
2	Date	Yes or No			Month	Result
3					Jan-21
4					Feb-21
5					Mar-21
6					Apr-21
7	12/6/2021	yes			May-21	0
8					Jun-21
9					Jul-21
10					Aug-21
11					Sep-21
12	4/16/2021	Yes			Oct-21	0
13					Nov-21
14					Dec-21
15	11/18/2021	Yes
16	11/20/2021	Yes
Sheet2

Cell Formulas
Range	Formula
F3:F14	F3	=IF(Sheet1!$A3="","",SUMPRODUCT((MONTH(Sheet1!$A$3:$A$6000)=MONTH(E3))*(YEAR(Sheet1!$A$3:$A$6000)=YEAR(E3))*(Sheet1!$B$3:$B$6000="yes")))

 

[JoeMo]

I suggest you re-read post #10 again, the Result formula belongs in Sheet2 NOT Sheet1. If that doesn't fix things maybe copy the two sheets from post 10 and use formula evaluation to see how the formula works.

 

[searchingforhelp]

I suggest you re-read post #10 again, the Result formula belongs in Sheet2 NOT Sheet1. If that doesn't fix things maybe copy the two sheets from post 10 and use formula evaluation to see how the formula works.

Thank you. The formula is in sheet2 not sheet1. The last example I provided the formula is in sheet2. I have been working with the formula you provided in sheet2 because to your point that is where the result is. The formula cannot be in sheet1 because that is where the data is populated. If you cannot help anymore, I understand. Thank you.

 

[JoeMo]

Post #11 seems to have Sheet1 and Sheet2 on a single sheet - what's up with that?

 

[searchingforhelp]

Hi Joe,

I was trying to make it visually easier for you to follow verses having two separate images as in post #10. Post # 11 is the same formula you provided in Post #10. It is not altered and works perfectly with the data in sheet1 that I provided in post #10.

However, when replacing the data in sheet1 Post # 10; specifically, column A and B, the same formula provided in Post #10 (sheet2) stops working. See example below.

Calendar Days.xlsx
	A	B
1	Sheet 1
2	Date	Yes or No
3
4
5
6
7	12/6/2021	yes
8
9
10
11
12	4/16/2021	Yes
13
14
15	11/18/2021	Yes
16	11/20/2021	Yes
Sheet1

Calendar Days.xlsx
	A	B	C
1	Sheet 2
2		Month	Result
3		Jan-21
4		Feb-21
5		Mar-21
6		Apr-21
7		May-21	0
8		Jun-21
9		Jul-21
10		Aug-21
11		Sep-21
12		Oct-21	0
13		Nov-21
14		Dec-21
Sheet2

Cell Formulas
Range	Formula
C3:C14	C3	=IF(Sheet1!$A3="","",SUMPRODUCT((MONTH(Sheet1!$A$3:$A$6000)=MONTH(B3))*(YEAR(Sheet1!$A$3:$A$6000)=YEAR(B3))*(Sheet1!$B$3:$B$6000="yes")))

 

[JoeMo]

My error on the formula. Make this change in cell C3 of Sheet2 and copy down.

=IF(B3="","",SUMPRODUCT((MONTH(Sheet1!$A$3:$A$6000)=MONTH(B3))*(YEAR(Sheet1!$A$3:$A$6000)=YEAR(B3))*(Sheet1!$B$3:$B$6000="yes")))

 

[searchingforhelp]

Hi Joe, thank you immensely for your time and dedication in helping me with this formula. There are no words that can fully express my gratitude. The formula is working with no issues . Have an excellent 2022 .

 

[JoeMo]

Hi Joe, thank you immensely for your time and dedication in helping me with this formula. There are no words that can fully express my gratitude. The formula is working with no issues . Have an excellent 2022 .

You are welcome - thanks for the reply.