count number of rows that falls in 2014

[fahadmohsin]

Hi,

I have an excel sheet which contains huge data, almost 60k rows, I want to count that how many rows are in 2014.

E.g. March- 04, 2014. June 16, 2015.

Due to some reason I don't want to sort the dates nor apply filters. I thought of combining countif with right formula but didn't get the desired result.

Can any one help me here

[chullan88]

Hi,use the formula :

=SUMPRODUCT(--(YEAR(Range)=2014))

where Range is the column containing dates.

[fahadmohsin]

Thanks for the reply.... but it didn't solve the whole problem. The issue is that some of the dates are not formatted correctly and there are some unusual dates like December 35, 2014. I think that this formula will not count these, right???

So it would really help me if a formula contains RIGHT in it.

[chullan88]

Assuming that all dates have the year at the end and there is a full stop also (Eg: 2014.)
use the formula

=SUMPRODUCT(--(LEFT(RIGHT(Range,5),4)="2014"))

[Khalidngo]

Hi Fahad,

Adding this a non-array solution:

=COUNTIFS(A2:A28,">=1/1/2014",A2:A28,"<=31/12/2014")+COUNTIF(A2:A28,"*2014*")

Regards,

[DannyJ]

Missed the criteria on this. Above works anyway.

[fahadmohsin]

I tried this but it didn't work, There is no "." in the dates so I amended the formula but result is 0.

[Pete_UK]

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("2014",range))))

You need to put in the range where your dates occur, e.g. D2:D2000

Hope this helps.

Pete

[chullan88]

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("2014",range))))

You need to put in the range where your dates occur, e.g. D2:D2000

Hope this helps.

Pete

That should nail it !!!!

[chullan88]

The formula using RIGHT doesn't work when it is formatted as date.
Please post sample file.

[Khalidngo]

Hi Fahad,

Post a sample excel file with some example of various type of dates.

Regards,

[fahadmohsin]

Here is a sample sheet

[Pete_UK]

You can use this array* formula for your sample file:

=SUM(IF(ISNUMBER(A2:A28),IF(YEAR(A2:A28)=2014,1,0),IF(RIGHT(A2:A28,4)="2014",1,0)))

Change the range (A2:A28, which occurs 3 times in the formula) to suit your real file.

*Note: an array formula needs to be committed using the key combination Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

Hope this helps.

Pete

[Pete_UK]

Change the entry in A13 to this:

38/07/2014

John's formula does not count it.

Pete

[JohnTopley]

Try

=SUMPRODUCT(--(IFERROR(YEAR(A2:A28)=2014,0)))

Enter with Ctrl+Shift+enter

[chullan88]

Try

=SUMPRODUCT(--(IFERROR(YEAR(A2:A28)=2014,0)))

Enter with Ctrl+Shift+enter

Works well!!

[fahadmohsin]

Thank you sooooo much.....

[JohnTopley]

Note: Pete's formula counts "rogue" dates e.g. 32/12/2014 whereas mine ignores them so I would advise using Pete's formula.

Or better: find and correct the rogue dates.

[JohnTopley]

@Pete: beat me to it!!!