Count the Number of Dates

[dparteka]

A1 thru A100 have dates, I'm looking for a formula that will count the number of dates that are between and including 01/01/14 thru 01/31/14... thank-you

 

[Luke M]

=SUMPRODUCT(1*(TEXT(A1:A100,"mmyyyy")="012014"))

 

[Somendra Misra]

@dparteka

Considering your dates in A1:A150. Use below formula
=COUNTIFS($A$1:$A$150,">="&$D$1,$A$1:$A$150,"<="&$E$1)

Put your lower date in D1 and upper date in E1.

Regards,

 

[dparteka]

@dparteka

Considering your dates in A1:A150. Use below formula
=COUNTIFS($A$1:$A$150,">="&$D$1,$A$1:$A$150,"<="&$E$1)

Put your lower date in D1 and upper date in E1.

Regards,

Thank you for your reply, I will not be able to put dates in D1 & E1 because I actualy have a table with 120 fields that covers a ten year period. I'm looking for something like the formual below which I have not been able to get to work, any ideas on this kind of approach?

This would be the first formual field for Jan 2014:
=COUNTIFS('List of Items'!A2:'List of Items'!A2000,">="&DATE(2014,1,1),'List of Items'!A2:'List of Items'!A2000,"<="&DATE(2014,12,31))

This would be the second formual field Feb 2014: =COUNTIFS('List of Items'!A2:'List of Items'!A2000,">="&DATE(2014,2,1),'List of Items'!A2:'List of Items'!A2000,"<="&DATE(2014,2,28))

 

[Somendra Misra]

@dparteka

Your Jan Formula has month = 12 in second criteria. Just check that.

Regards,

 

[dparteka]

@dparteka

Your Jan Formula has month = 12 in second criteria. Just check that.

Regards,

Sorry, type error, the formual is as shown below and does not work, it returns #NAME?

=COUNTIFS('List of Items'!A2:'List of Items'!A2000,">="&DATE(2014,1,1),'List of Items'!A2:'List of Items'!A2000,"<="&DATE(2014,1,31))

 

[Somendra Misra]

I think the way you are typing the range is wrong. Just check it.

 

[Luke M]

If you need it to be dynamic, my formula can become:
=SUMPRODUCT(1*(TEXT(A1:A100,"mmyyyy")=TEXT(DATE(2014,ROW($A1),1),"mmyyyy"))
First cell would find all Jan 2014, then Feb 2014, then March, etc.

 

[NARAYANK991]

Hi ,

If you do not have Excel 2007 or later versions , you cannot use the ...IFS counterparts of the ...IF functions.

Even if you do have , try this :

=COUNTIFS('List of Items'!A2:A2000,">="&DATE(2014,1,1),'List of Items'!A2:A2000,"<="&DATE(2014,1,31))

Narayan

 

[dparteka]

Hi ,

If you do not have Excel 2007 or later versions , you cannot use the ...IFS counterparts of the ...IF functions.

Even if you do have , try this :

=COUNTIFS('List of Items'!A2:A2000,">="&DATE(2014,1,1),'List of Items'!A2:A2000,"<="&DATE(2014,1,31))

Narayan

Thanks for the help, if can beleive it we're still using Excel 2003, do I have any other options?

 

[Luke M]

Thanks for the help, if can beleive it we're still using Excel 2003, do I have any other options?

Not to sound like a squeeky wheel, but have you tried my solution? The TEXT function lets you be the most versatile when handling date criteria, and it works in XL 2003.

 

[dparteka]

Not to sound like a squeeky wheel, but have you tried my solution? The TEXT function lets you be the most versatile when handling date criteria, and it works in XL 2003.

I really appreciate your help with this and I did try it but I might be missing something here. I have a table with 10 YEAR columns and 12 MONTH rows so the very first cell houses only the Jan 2013 count, next cell down houses Feb 2013 and so on. Based on what you provided my formula looks like this...

=SUMPRODUCT(1*(TEXT('List of Items'!A2:'List of Items'!A2000,"mmyyyy")=TEXT(DATE(2013,ROW('List of Items'!$A2),1),"mmyyyy")))

Am I correct in saying that putting this formula in the first cell should produce a count for Jan 2013 which it does not do and even if it did what would the formula look like in the next cell down for Feb 2013 or the next to the right for 2014?

 

[NARAYANK991]

Hi ,

Change your formula to :

=SUMPRODUCT(1*(TEXT('List of Items'! $A$2:$A$2000,"mmyyyy")=TEXT(DATE(2013,ROW($A1),1),"mmyyyy")))

The $ signs in $A$2:$A$2000 are necessary because otherwise as you copy it downwards , this will change to A3:A2001 , A4:A2002 and so on.

Secondly , the ROW(A1) part returns 1 , which gives DATE(2013,1,1) for January ; when you copy this down to the next row , this will become ROW(A2) to give DATE(2013,2,1) for February , and so on.

For 2014 , replace the 2013 by 2014 ; otherwise if you want this too to become dynamic , you can do it , but the formula will become somewhat lengthier.

Narayan

 

[Luke M]

what would the formula look like in the next cell down for Feb 2013 or the next to the right for 2014?

If you want years to increment as we move to the right, and months as we move down, formula is:
=SUMPRODUCT(1*(TEXT('List of Items'! $A$2:$A$2000,"mmyyyy")=
TEXT(DATE(2013+COLUMN(A$1)-1,ROW($A1),1),"mmyyyy")))

You can now copy this formula down and to the right, as desired.

 

[dparteka]

Hi ,

Change your formula to :

=SUMPRODUCT(1*(TEXT('List of Items'! $A$2:$A$2000,"mmyyyy")=TEXT(DATE(2013,ROW($A1),1),"mmyyyy")))

The $ signs in $A$2:$A$2000 are necessary because otherwise as you copy it downwards , this will change to A3:A2001 , A4:A2002 and so on.

Secondly , the ROW(A1) part returns 1 , which gives DATE(2013,1,1) for January ; when you copy this down to the next row , this will become ROW(A2) to give DATE(2013,2,1) for February , and so on.

For 2014 , replace the 2013 by 2014 ; otherwise if you want this too to become dynamic , you can do it , but the formula will become somewhat lengthier.

Narayan

Narayan... works great, thanks for the detailed explanation, you guys are awesome

 

[dparteka]

If you want years to increment as we move to the right, and months as we move down, formula is:
=SUMPRODUCT(1*(TEXT('List of Items'! $A$2:$A$2000,"mmyyyy")=
TEXT(DATE(2013+COLUMN(A$1)-1,ROW($A1),1),"mmyyyy")))

You can now copy this formula down and to the right, as desired.

Luke M… amazing, works like magic, I can’t tell you enough how much I appreciate the help… you guys are awesome