count values accross multiple columns

[emearley]

Hi,,

I am trying to count occurances of dates accross a set of data in multiple columns.. I am using this to understand future workloads based on date due of work orders and varying frequencies. I have seven due date columns and my data set basically looks like this.

Due date 1 Due date 2 Due date 3
12/12/2012 12/13/2012 12/14/2012
12/13/2012 12/14/2012 12/15/2012
12/14/2012 12/15/2012 12/16/2012

What I would like to do is count how many times each individual date occurs. What I have been reading is that a countif function is the closest thing use but i have not been sucessful in getting the results i need. I have also tried various pivot table configurations and that is also not working. I am an average user of excel would need some assistance.

Thanks..

 

[Robert Mika]

Hi,,

I am trying to count occurances of dates accross a set of data in multiple columns.. I am using this to understand future workloads based on date due of work orders and varying frequencies. I have seven due date columns and my data set basically looks like this.

Due date 1 Due date 2 Due date 3
12/12/2012 12/13/2012 12/14/2012
12/13/2012 12/14/2012 12/15/2012
12/14/2012 12/15/2012 12/16/2012

What I would like to do is count how many times each individual date occurs. What I have been reading is that a countif function is the closest thing use but i have not been sucessful in getting the results i need. I have also tried various pivot table configurations and that is also not working. I am an average user of excel would need some assistance.

Thanks..

You can extract unique item to one column(copy/paste one column under another and then use use Remove Duplicates tool from Excel option) and the use

Excel 2010
	A	B	C	D	E	F	G	H
1	Due date 1	Due date 2	Due date 3			9		All entries
2	12/12/2012	12/13/2012	12/14/2012		12/12/2012	1		9
3	12/13/2012	12/14/2012	12/15/2012		12/13/2012	2
4	12/14/2012	12/15/2012	12/16/2012		12/14/2012	3
5					12/15/2012	2
6					12/16/2012	1
Sheet1

Cell Formulas
Range	Formula
F1	=SUM(F2:F6)
F2	=COUNTIF($A$2:$C$4,E2)
H2	=COUNTA(A2:C4)

The COUNTA formula is here just to make sure that you have got all items conted.

 

[emearley]

I think I have found my answer... a little sloppy in my opinion but it seemed to work.. . I just took all my columns and used copy > paste options > Paste link and put all dates in a single row (over 10000 rows) and then just pivoted off the long list of dates. I guess I was overthinking what I needed to do.