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

searchingforhelp

Board Regular
Joined
Nov 11, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Hi all,

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

Calendar Days.xlsx
ABCD
1DateCategoryMonthTotal by Month
25/10/2021YesJan-210
37/3/2021YesFeb-210
49/21/2021YesMar-210
512/12/2021NoApr-211
64/15/2021NoMay-214
75/20/2021YesJun-210
85/25/2021YesJul-212
912/1/2021NoAug-210
105/6/2021NoSep-211
117/15/2021NoOct-210
Sheet5
Cell Formulas
RangeFormula
D2:D11D2=COUNTIFS(A:A,">="&C2,A:A,"<="&EOMONTH(C2,0))
 
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
ABCDEF
1Sheet 1Sheet 2
2DateYes or NoMonthResult
3Jan-21 
4Feb-21 
5Mar-21 
6Apr-21 
712/6/2021yesMay-210
8Jun-21 
9Jul-21 
10Aug-21 
11Sep-21 
124/16/2021YesOct-210
13Nov-21 
14Dec-21 
1511/18/2021Yes
1611/20/2021Yes
Sheet2
Cell Formulas
RangeFormula
F3:F14F3=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")))
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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.
 
Upvote 0
Post #11 seems to have Sheet1 and Sheet2 on a single sheet - what's up with that?
 
Upvote 0
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
AB
1Sheet 1
2DateYes or No
3
4
5
6
712/6/2021yes
8
9
10
11
124/16/2021Yes
13
14
1511/18/2021Yes
1611/20/2021Yes
Sheet1


Calendar Days.xlsx
ABC
1Sheet 2
2MonthResult
3Jan-21 
4Feb-21 
5Mar-21 
6Apr-21 
7May-210
8Jun-21 
9Jul-21 
10Aug-21 
11Sep-21 
12Oct-210
13Nov-21 
14Dec-21 
Sheet2
Cell Formulas
RangeFormula
C3:C14C3=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")))
 
Upvote 0
My error on the formula. Make this change in cell C3 of Sheet2 and copy down.

VBA Code:
=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")))
 
Upvote 0
Solution
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 (y). Have an excellent 2022 :) .
 
Upvote 0
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 (y). Have an excellent 2022 :) .
You are welcome - thanks for the reply.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top