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

 

[JoeMo]

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

Cell Formulas
Range	Formula
D2:D11	D2	=SUMPRODUCT((MONTH($A$2:$A$11)=MONTH(C2))*($B$2:$B$11="Yes"))

 

[searchingforhelp]

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

Cell Formulas
Range	Formula
D2:D11	D2	=SUMPRODUCT((MONTH($A$2:$A$11)=MONTH(C2))*($B$2:$B$11="Yes"))

Hi Joe,

Thank you for your help. I tried to adjust the formula to capture the entire column as the data I am working with is not limited to 10 rows. Unfortunately, I cannot get it to work. See below

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

Cell Formulas
Range	Formula
D2:D11	D2	=SUMPRODUCT((MONTH(A:A)=MONTH(C2))*(B:B="Yes"))

 

[JoeMo]

Don't use full columns. Just use a range that easily exceeds the maximum you might ever use. For example, say the max is 5000 rows in col A. Then you could use this in cell C2:

=IF($A2="","",SUMPRODUCT((MONTH($A$2:$A$6000)=MONTH(C2))*($B$2:$B$6000="Yes")))
drag the formula down to A6000.

BTW: this will count all "Yes" entries in the particular month, regardless of year. If you may have multiple year values included in the dates in col A and you only wish to count those in say 2021 you need to add another condition, like this:
=IF($A2="","",SUMPRODUCT((MONTH($A$2:$A$6000)=MONTH(C2))*(YEAR($A$2:$A$6000)=YEAR(C2))*($B$2:$B$6000="Yes")))

 

[searchingforhelp]

Don't use full columns. Just use a range that easily exceeds the maximum you might ever use. For example, say the max is 5000 rows in col A. Then you could use this in cell C2:

=IF($A2="","",SUMPRODUCT((MONTH($A$2:$A$6000)=MONTH(C2))*($B$2:$B$6000="Yes")))
drag the formula down to A6000.

Don't use full columns. Just use a range that easily exceeds the maximum you might ever use. For example, say the max is 5000 rows in col A. Then you could use this in cell C2:

=IF($A2="","",SUMPRODUCT((MONTH($A$2:$A$6000)=MONTH(C2))*($B$2:$B$6000="Yes")))
drag the formula down to A6000.

BTW: this will count all "Yes" entries in the particular month, regardless of year. If you may have multiple year values included in the dates in col A and you only wish to count those in say 2021 you need to add another condition, like this:
=IF($A2="","",SUMPRODUCT((MONTH($A$2:$A$6000)=MONTH(C2))*(YEAR($A$2:$A$6000)=YEAR(C2))*($B$2:$B$6000="Yes")))

BTW: this will count all "Yes" entries in the particular month, regardless of year. If you may have multiple year values included in the dates in col A and you only wish to count those in say 2021 you need to add another condition, like this:
=IF($A2="","",SUMPRODUCT((MONTH($A$2:$A$6000)=MONTH(C2))*(YEAR($A$2:$A$6000)=YEAR(C2))*($B$2:$B$6000="Yes")))

Thank you Joe for your time. Perhaps providing a little background may help. I have two tabs in a workbook. I periodically pull data from an application and dump the results in Excel format in the first tab. The old data is completely removed/deleted and the new data takes its place. In the second tab I have formulas that automatically calculate what I need from the first tab. The data size can very from the periodic data pull. I am having difficulty using this formula in retrieving what is needed from the first tab.

Here is an example:

=IF('First Tab'!$A2="","",SUMPRODUCT((MONTH('First Tab'!$A$2:$A$6000)=MONTH('First Tab'!(C2))*('First Tab'!$B$2:$B$6000="yes")))

Again, thank you for your time.

 

[JoeMo]

Why no mention of this in your OP? And what exactly is the "difficulty" you are experiencing? Perhaps you can use XL2BB to show a portion of the layouts in both tabs. Is cell C2 in the first tab or is it the second tab?

Thank you Joe for your time. Perhaps providing a little background may help. I have two tabs in a workbook. I periodically pull data from an application and dump the results in Excel format in the first tab. The old data is completely removed/deleted and the new data takes its place. In the second tab I have formulas that automatically calculate what I need from the first tab. The data size can very from the periodic data pull. I am having difficulty using this formula in retrieving what is needed from the first tab.

Here is an example:

=IF('First Tab'!$A2="","",SUMPRODUCT((MONTH('First Tab'!$A$2:$A$6000)=MONTH('First Tab'!(C2))*('First Tab'!$B$2:$B$6000="yes")))

Again, thank you for your time.

 

[searchingforhelp]

Why no mention of this in your OP? And what exactly is the "difficulty" you are experiencing? Perhaps you can use XL2BB to show a portion of the layouts in both tabs. Is cell C2 in the first tab or is it the second tab?

Thank you for your reply and I truly appreciate the time you are taking to help me. I thought if I can get help with the basics, I would be able to figure out the rest. A learning experience for me.

Sheet1 has the Date, Yes or No and the Months. Sheet 2 only has the Months and Results. The difficulty is when I attempt to have the formula read the information in sheet 1, the results are N/A

Calendar Days.xlsx
	A	B	C
1	Sheet 1
2	Date	Yes or No	Month
3	5/10/2021	Yes	Jan-21
4	7/3/2021	Yes	Feb-21
5	9/21/2021	Yes	Mar-21
6	12/12/2021	No	Apr-21
7	4/15/2021	No	May-21
8	5/20/2021	Yes	Jun-21
9	5/25/2021	Yes	Jul-21
10	12/1/2021	No	Aug-21
11	5/6/2021	No	Sep-21
12	7/15/2021	No	Oct-21
13	12/15/2021	Yes	Nov-21
14	11/27/2021	No	Dec-21
Sheet1

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

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

 

[searchingforhelp]

Thank you for your reply and I truly appreciate the time you are taking to help me. I thought if I can get help with the basics, I would be able to figure out the rest. A learning experience for me.

Sheet1 has the Date, Yes or No and the Months. Sheet 2 only has the Months and Results. The difficulty is when I attempt to have the formula read the information in sheet 1, the results are N/A. And does the formula compensate for black cells?

Calendar Days.xlsx
	A	B	C
1	Sheet 1
2	Date	Yes or No	Month
3	5/10/2021	Yes	Jan-21
4	7/3/2021	Yes	Feb-21
5	9/21/2021	Yes	Mar-21
6	12/12/2021	No	Apr-21
7	4/15/2021	No	May-21
8	5/20/2021	Yes	Jun-21
9	5/25/2021	Yes	Jul-21
10	12/1/2021	No	Aug-21
11	5/6/2021	No	Sep-21
12	7/15/2021	No	Oct-21
13	12/15/2021	Yes	Nov-21
14	11/27/2021	No	Dec-21
Sheet1

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

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

Thank you for your reply and I truly appreciate the time you are taking to help me. I thought if I can get help with the basics, I would be able to figure out the rest. A learning experience for me.

Sheet1 has the Date, Yes or No and the Months. Sheet 2 only has the Months and Results. The difficulty is when I attempt to have the formula read the information in sheet 1, the results are N/A

Calendar Days.xlsx
	A	B	C
1	Sheet 1
2	Date	Yes or No	Month
3	5/10/2021	Yes	Jan-21
4	7/3/2021	Yes	Feb-21
5	9/21/2021	Yes	Mar-21
6	12/12/2021	No	Apr-21
7	4/15/2021	No	May-21
8	5/20/2021	Yes	Jun-21
9	5/25/2021	Yes	Jul-21
10	12/1/2021	No	Aug-21
11	5/6/2021	No	Sep-21
12	7/15/2021	No	Oct-21
13	12/15/2021	Yes	Nov-21
14	11/27/2021	No	Dec-21
Sheet1

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

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

And one more thing I noticed is when the fields are blank in column A or B the results in column D is empty. can it result in a zero instead?

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

Cell Formulas
Range	Formula
D3:D14	D3	=IF($A3="","",SUMPRODUCT((MONTH($A$3:$A$6000)=MONTH(C3))*(YEAR($A$3:$A$6000)=YEAR(C3))*($B$3:$B$6000="Yes")))

 

[searchingforhelp]

And one more thing I noticed is when the fields are blank in column A or B the results in column D is empty. can it result in a zero instead?

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

Cell Formulas
Range	Formula
D3:D14	D3	=IF($A3="","",SUMPRODUCT((MONTH($A$3:$A$6000)=MONTH(C3))*(YEAR($A$3:$A$6000)=YEAR(C3))*($B$3:$B$6000="Yes")))

My apologies, I was not clear. For some reason when one of the cells are blank the formula does not calculate. In my data set, the first 103 rows are blank. The formula did not calculate when row 104 had the date and Yes. Sorry for many emails.

 

[JoeMo]

First, I would leave the result cell empty (blank) if one of the cells in Sheet1 col A is blank. Otherwise, you end up with 0 all the way from your last data row to row 6000. Below is what I think you want to achieve. Note that the result cells are in Sheet2 NOT Sheet1. Note also that you need to adjust addresses in the formula to be consistent with your data layouts in the two sheets.

Book1
	A	B	C
1	Sheet 1
2	Date	Yes or No	Month
3	5/10/2021	Yes	January-21
4	7/3/2021	Yes	February-21
5	9/21/2021	Yes	March-21
6	12/12/2021	No	April-21
7	4/15/2021	No	May-21
8	5/20/2021	Yes	June-21
9	5/25/2021	Yes	July-21
10	12/1/2021	No	August-21
11	5/6/2021	No	September-21
12	7/15/2021	No	October-21
13	12/15/2021	Yes	November-21
14	11/27/2021	No	December-21
Sheet1

Book1
	A	B	C
1	Sheet 2
2		Month	Result
3		January-21	0
4		February-21	0
5		March-21	0
6		April-21	0
7		May-21	3
8		June-21	0
9		July-21	1
10		August-21	0
11		September-21	1
12		October-21	0
13		November-21	0
14		December-21	1
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")))