Sort + Count in Excel 2010

[philb99]

Hi -I am struggling with Sorting + Counting in Excel 2010

I have 60,000 rows of data with an Invoice number and Date

I would like to know how many invoices in the individual years, as they go back over 20 years

Is this something simple as I am not having much luck with Data / Sort - as this appears to be the long way around

Thanks

 

[Joe4]

If your dates and actual date entries, you can count the number of entries for any year like this:

=SUMPRODUCT(--(YEAR(A$1:A$100)=2012))

If you list your years down a column, starting in cell D1, then you could just use the formula:

=SUMPRODUCT(--(YEAR(A$1:A$100)=D1))

and copy down.

 

[philb99]

Thanks Jo4, my date is formatted 01/01/2017 etc will the formulae still work

 

[Joe4]

It does not matter what format you have chosen for the dates, just as long as your entries are "date" entries and not entered as "text".
Test it out and see.

 

[philb99]

HiJoe4 - its perfect down to about row 30000 then just stops working -any reason as I have over 60,000 row Also I tried sorting Z to A but his only appears to have worked down a certain line of data - Strange

 

[Joe4]

It may be a silly question, but I will ask it anyway.
Have you adjusted your formulas to account for all your rows?

 

[philb99]

Thanks Joe4 - Yes but its always good to check lol

 

[Joe4]

So what exactly does it do?

Are you saying that when you use the SUMPRODUCT formula I gave you, it doesn't count any rows past 30000?

 

[philb99]

Thanks - Resolved now - located a rogue date - appreciate your help