Countif Formula for Month and Date

Hi,

I am trying to count the number of times a date/year occur in a list. I have a monthly list from 11/1/2009-10/1/2017 in column L2:L97, and trying to count the number of instances the month and year show up in columns B2:B97. Any ideas how to create this formula?

Thanks

It would help to see a sample of the data (mostly to see what the dates in column B look like and where you would like to put your formula).

That being said, this general formula should work:

M2 =SUMPRODUCT((YEAR(B$2:B$97)=YEAR(L2))*(MONTH(B$2:B$97)=MONTH(L2)))

If that doesn't give you the desired results, please upload a small representative sample of your data along with the desired outcome of the formulas (manually entered).

Another option would be something along the lines of this:

M2 =COUNTIFS(B$2:B$97,">="&L2,B$2:B$97,"<="&EOMONTH(L2,0))

thank you, both worked. I am attaching a sheet. I am trying to figure out how to add to the formula. I believe it needs to have 3 layers to it. I have provided a manual number in column H. I am trying to count what positions are open.

1) Count if the Month/Year in B matches G, then look to step 2
2) If the position is exited, is the date in column C less than or equal to the date in G. If so, it should not be counted. If not, it should still be counted.
3) If the exit date is blank, it means the position is still open, so count it as open.

Your description is confusing and your columns are shifted (G in your description from post #4 = H in your sample from post #4).

The following is referring to the ranges as they are in your sample (not description).

If you want to count the number of instances where the date in column H falls between the dates in columns B and C (but using the first of the month for column B), that would look like this:

J2 =SUMPRODUCT((DATE(YEAR(B$2:B$10),MONTH(B$2:B$10),1)<=H2)*(C$2:C$10>H2))

Note that I put "open" in cells C4, C8, and C10. Text is seen as greater than any number.

There are a few numbers that are off from your manual results. Can you explain why I20 should be 6 and not 7, why I23 should be 4 and not 6, etc.?

See attachment.

I20 should be 6 because a position was exited on 6/8/11, so it should not be included in date 6/1, since it was the same month. It should be at the end of the month what is my open total

Thank you, that workds. can you explain the formula. not sure what the 1 and zeros do.

Sure, the formula counts the number of instances where the date in column B is less than or equal to EOMONTH(H2,0) and where the date in column C is greater than EOMONTH(H2,0)+1.

EOMONTH(H2,0) returns the last day of the month of H2 i.e. 12/31/2009

EOMONTH(H2,0)+1 returns the day after the last day of the month of H2 i.e. 1/1/2010

Thanks for the rep! If that solved your original question, please mark this thread as SOLVED.

that doesn't work in my non sample sheet (a lot more data), because it's double counting years with same months.

Then I recommend that you upload a representative sample of your data. It doesn't have to include a lot of data, just enough to show the problem that you are experiencing.

here you go

Either my understanding of the situation is not correct or you are changing the requirements.

Either way, I'll start from scratch since I am really not understanding the logic here.

Based on the updated sample that you shared in post #12, why should G4 (1/1/2010) = 2?

Looking at columns B and C, shouldn't Jan 2010 match with rows 2, 3, and 50?

That is, 11/17/2009 - 5/11/2016, 12/10/2009 - 1/11/2012, and 9/11/2007 - Open.

G4 should equal 2, because at the time of 12/1/19, B2, and B3 were still open positions (they do not get removed until 5/11/16, and 1/11/12).

sorry, it should be 3. Apologies. Let me do a quick check before closing thread.

Apologies. G4 should be 2 because on 1/1/10, only b2 and b3 were open. B4 wouldn't be counted until 2/1/10 (F5). Also, b2 and b3 wouldn't be counted as closed positions until 1/11/12 and 5/11/16. I hope this clarifies?

What about B50?

Thank you for pointing that out. I fat fingered the date. When I change that to 2017 all ties. Apologize for wasting your time, and thank you for being so patient. I will close this out.

No worries, glad that we were able to help.