Formula to count dates for a certain month and year based on criteria in diff. column

D

distillerjunkie

New member
Joined
Mar 16, 2016
Messages
33
Reaction score
0
Points
0
I need a formula to count dates in a column B that are only in the month of May of 2016 based on whether it is "Open" or "Closed" in another column G. I currently have an array to count the dates only in the month of May but need to add only for the year 2016 and only for either Open" in one formula and only "Closed" in a separate formula (for separate calculations) in another column G. This is what I have currently: {=SUM(IF(MONTH(A_N_HIT_2016[Date Submitted])=5,1))} This works just to return he count of dates only in the month of May but I need to formula to go one step further to count only the dates in the month of May if they are "Open" (and only "Closed" in a separate formula). I have attached the spreadsheet for reference.

Thanks!
 

Attachments

  • HIT_2016_forum.xlsm
    301.3 KB · Views: 13
Something like =SUMPRODUCT((MONTH(A_N_HIT_2016[Date Submitted])=5)*(A_N_HIT_2016[Current Stage]="closed")) would do. I didn't check it thoroughly yet
 
addition?

Would it be too much to ask for a formula to add both Investigating and Implementing?
 
Be aware that formulae so far look for the month of May, but do not specify a year (you sheet has dates going back to 2014, but fortunately dates in May only occur in 2016)
This formula is specific to the year, but can return counts between any two dates:
=COUNTIFS(A_N_HIT_2016[Current Stage],"closed",A_N_HIT_2016[Date Submitted],">=" & DATE(2016,5,1),A_N_HIT_2016[Date Submitted],"<" & DATE(2016,6,1))
which looks for dates greater or equal to 1st May 2016 and less than 1st June 2016.
You can substitute other strings instead of "closed".
You can also get a count of dates for anything but "closed" with the likes of:
=COUNTIFS(A_N_HIT_2016[Current Stage],"<>closed",A_N_HIT_2016[Date Submitted],">=" & DATE(2016,5,1),A_N_HIT_2016[Date Submitted],"<" & DATE(2016,6,1))
 
new formula's

What is the first formula counting? closed for May of 2016?

The 2nd formula counts anything other than closed for may of 2016 right?
 
I have this to count the total number in May but this does not count only May of 2016 correct? Is there a way to count only May of 2016?

=SUM(IF(MONTH(A_N_HIT_2016[Date Submitted])=5,1))
 
=SUMPRODUCT((MONTH(A_N_HIT_2016[Date Submitted])=5)*(YEAR(A_N_HIT_2016[Date Submitted])=2016)*(A_N_HIT_2016[Current Stage]="closed"))


You could also use a Pivot Table depending on desired outcome
 
Is there a formula that would count everything (Investigating, Implementing and Closed) for May of 216?
 
distillerjunkie said:
Is there a formula that would count everything (Investigating, Implementing and Closed) for May of 216?
Click to expand...


=COUNTIFS(A_N_HIT_2016[Date Submitted],">=" & DATE(2016,5,1),A_N_HIT_2016[Date Submitted],"<" & DATE(2016,6,1))
 
You must log in or register to reply here.
Back
Top