Data search and corellation

Closed
Car7os Posts 2 Registration date Tuesday June 18, 2013 Status Member Last seen June 18, 2013 - Jun 18, 2013 at 08:24 AM
Car7os Posts 2 Registration date Tuesday June 18, 2013 Status Member Last seen June 18, 2013 - Jun 18, 2013 at 09:34 AM
Hello

I have hundreds of pages of data that I am going through for Pie and Pareto analysis and I am trying to speed up the process by using formulas instead of me manually having to filter through it every day.

I have 3 columns ...

A - Date
B - Problem Duration
C - Problem Code (30 codes)

for example
A ----------- B -- C
01/06/13 - 20 - 7
01/06/13 - 10 - 28
01/06/13 - 50 - 1
02/06/13 - 90 - 7
02/06/13 - 15 - 5

And I am tring to use a summary sheet where I can input a date range it it will automatically tell me the number of times a code appeared and the total duration for that inputted date range

For example ....

Range - 01/06/13 to 02/16/13

Code 1 - 50
Code 5 - 15
Code 7 - 110
Code 28 - 10

If anyone could help it would be amazing as it's driving me mad going through the data manually every day!

I'm using Excel 2010


Regards

Carl

1 reply

Car7os Posts 2 Registration date Tuesday June 18, 2013 Status Member Last seen June 18, 2013
Jun 18, 2013 at 09:34 AM
I've found this .....

=SUMIFS(C2:C100,A2:A100,"1",B2:B100,">=01/01/2013",B2:B100,"<=31/03/2013")

But need to change the dates in the formula to cell locations so I can change the dates when required, can someone help me with this as I am fairly new to excel.

Cheers
0