CODEWITHSUNDEEP
excelexcel-formula
James
James

Reputation: 33

Count the number of times a value appears between two dates

I'd like to write a formula that counts the number of times the value in cell A on Sheet 1 shows up in a column on Sheet 2, but between the enrollment date in another column on Sheet 1, and that date plus one year. I think you could use a countifs statement, with a edate in there, but I haven't been able to get it to work. Examples of the two sheets are below. My thinking was something like:

=COUNTIFS('Coaching Sessions'!A:A,'Program Engagement'!A3, 'Coaching Sessions'!D:D, G2,'Coaching Sessions'!D:D, EDATE(G2, 12))

But that returns a 0, when it should be 48 (the numbers filled in in my example below I started with criteria of , ">=10/15/2015" and , "<=10/15/2016", but then I realized that there were different start dates. The example in the formula bar is what I'd like it to do, it obviously doesn't work right now haha.

Any help would be appreciated! Thanks in advance, have a good day. enter image description here

enter image description here

Upvotes: 0

Views: 1508

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You need to use >= and <= as strings. The criteria in COUNTIFS resolve as strings so use "" and & to concatenate

=COUNTIFS('Coaching Sessions'!A:A,'Program Engagement'!A3, 'Coaching Sessions'!D:D, ">=" & G2,'Coaching Sessions'!D:D, "<=" & EDATE(G2, 12))

Upvotes: 1

Related Questions