Count the times a month/year appear in a list of data

**CliffyBiro** · 03-01-2018, 11:34 AM

Hi Guys,
Ive been trying to find out how to do this, Im sure its not as hard as Im making it!
I have a list of dates, there are 3 columns for different steps in my process.
The columns are New, Comm & Open.
The dates are in dd/mm/yyyy format and are from 01/01/2013.
If the date is before 01/01/2015 in the New column I want to ignore all 3 columns, then count the number of times the month/year is listed per column.
How do I do the with either a formula or most probably a pivot table?

For Example some of the list is below.
New Comm Open
1 08/07/2013 06/10/2014 10/06/2014
2 09/05/2016 02/02/2017 15/02/2017
3 24/04/2017 04/10/2017 29/11/2017
4 27/09/2013 19/03/2014 19/03/2014
5 15/07/2014 24/06/2015 06/08/2015
6 08/10/2014 26/03/2015 01/04/2015

Therefore Ignore all data in row 1, 4, 5 & 6. Then for the rest count the number of Month/Year occurs in each column.
I have about 500 rows of data.

**Special-K** · 03-01-2018, 11:57 AM

Ignore 1, 4, 5, 6 - ok
So result should be 2 ? Because there are 2 dates in the first column, count 2 dates in the other columns too ?

You're implying not all cells will be filled with dates - is that assumption correct ?
If so then

in D1
=COUNTIFS(A1:A500,">"&datevalue("01/01/2015"))
and copy across to F1

**CliffyBiro** · 03-02-2018, 07:08 AM

Hi Special-K,
Thanks for replying.
The formula you gave only counts the number of dates after 01/01/2015, what I need is to count the number of times all of the months are listed, so how many times is Jan2015, Feb 2015, Mar2015 etc etc etc listed.
Regards,
CliffyBiro

**JeteMc** · 03-05-2018, 11:40 PM

Assuming I understand the setup correctly, the proposed solution does what you want.
Added a column (D) which is populated using: =IF(A2>--"01/01/2015",1,0)
E1 is populated using: =EOMONTH(AGGREGATE(15,6,(A2:C7)/(D2:D7=1),1),-1)+1
F1:X1 are populated using: =DATE(YEAR(E1),MONTH(E1)+1,1)
E2:X2 are populated using: =SUMPRODUCT(($D2:$D7=1)*($A2:$C7>=E1)*($A2:$C7< DATE(YEAR(E1),MONTH(E1)+1,1)))
Let us know if you have any questions.