Exported data pulling into another spreadsheet

Good afternoon,

I am tracking downtime reason codes that have time associated with each
of them by date. I export data from Microsoft Infopath to a
spreadsheet, and then need to set up a spreadsheet that sums common
downtime occurrences by date. Here is an example of what is exported
to a spreasheet:

A B
C

1 08-01-06 Warm-Up
15
2 08-01-06 Tooling
60
3 08-02-06 Insert
5
4 08-02-06 Breaks
15
5 08-02-06 Breaks
30
6 08-03-06 Waiting
5
7 08-03-06 Breaks
15
8 08-04-06 Lot Change
16

I want to now separate this data by date and reason codes into another
spreadsheet:


A B
C D
08-01-06 08-02-06
08-03-06 08-04-06

1 Breaks 0 45
15 0
2 Warm-Up 15 0
0 0
3 Tooling 60 0
0 0
4 Insert 0 5
0 0
5 Waiting 0 0
5 0
6 Lot Change 0 0
0 16
7
8

What would the formula be to accomplish this?

Would the following get me on the right track? =if(b1:b8=Breaks, C
Value)????

I want the 08-02-06 column to sum the occurrences of like types of
downtimes (15 + 30). I know I am missing something. Please help.
Thanks.

Bemidji wrote:
> Good afternoon,
>
> I am tracking downtime reason codes that have time associated with each
> of them by date. I export data from Microsoft Infopath to a
> spreadsheet, and then need to set up a spreadsheet that sums common
> downtime occurrences by date. Here is an example of what is exported
> to a spreasheet:
>
> A B
> C
>
> 1 08-01-06 Warm-Up
> 15
> 2 08-01-06 Tooling
> 60
> 3 08-02-06 Insert
> 5
> 4 08-02-06 Breaks
> 15
> 5 08-02-06 Breaks
> 30
> 6 08-03-06 Waiting
> 5
> 7 08-03-06 Breaks
> 15
> 8 08-04-06 Lot Change
> 16
>
> I want to now separate this data by date and reason codes into another
> spreadsheet:
>
>
> A B
> C D
> 08-01-06 08-02-06
> 08-03-06 08-04-06
>
> 1 Breaks 0 45
> 15 0
> 2 Warm-Up 15 0
> 0 0
> 3 Tooling 60 0
> 0 0
> 4 Insert 0 5
> 0 0
> 5 Waiting 0 0
> 5 0
> 6 Lot Change 0 0
> 0 16
> 7
> 8
>
> What would the formula be to accomplish this?
>
> Would the following get me on the right track? =if(b1:b8=Breaks, C
> Value)????
>
> I want the 08-02-06 column to sum the occurrences of like types of
> downtimes (15 + 30). I know I am missing something. Please help.
> Thanks.


I am assuming I would use the =SUMIF formula, but I am still having
trouble separating by date. The layout of my post did not appear how I
typed it, there is the date, downtime reason, and minutes down, 3
separate columns, ABC.

Bemidji wrote:
> Good afternoon,
>
> I am tracking downtime reason codes that have time associated with each
> of them by date. I export data from Microsoft Infopath to a
> spreadsheet, and then need to set up a spreadsheet that sums common
> downtime occurrences by date. Here is an example of what is exported
> to a spreasheet:
>
> A B
> C
>
> 1 08-01-06 Warm-Up
> 15
> 2 08-01-06 Tooling
> 60
> 3 08-02-06 Insert
> 5
> 4 08-02-06 Breaks
> 15
> 5 08-02-06 Breaks
> 30
> 6 08-03-06 Waiting
> 5
> 7 08-03-06 Breaks
> 15
> 8 08-04-06 Lot Change
> 16
>
> I want to now separate this data by date and reason codes into another
> spreadsheet:
>
>
> A B
> C D
> 08-01-06 08-02-06
> 08-03-06 08-04-06
>
> 1 Breaks 0 45
> 15 0
> 2 Warm-Up 15 0
> 0 0
> 3 Tooling 60 0
> 0 0
> 4 Insert 0 5
> 0 0
> 5 Waiting 0 0
> 5 0
> 6 Lot Change 0 0
> 0 16
> 7
> 8
>
> What would the formula be to accomplish this?
>
> Would the following get me on the right track? =if(b1:b8=Breaks, C
> Value)????
>
> I want the 08-02-06 column to sum the occurrences of like types of
> downtimes (15 + 30). I know I am missing something. Please help.
> Thanks.


I am assuming I would use the =SUMIF formula, but I am still having
trouble separating by date. The layout of my post did not appear how I
typed it, there is the date, downtime reason, and minutes down, 3
separate columns, ABC.

A pivot table may be your best option. Click any cell in your data, then
select Data >> PivotTable and PivotChart Report. Make sure the correct range
is selected by Excel and tell it to put the output on a new worksheet. Add
the Downtime Reason to the Row Area, the Date to the Column Area, and the
Minutes Down to the Data Area. By default, the pivot table will use Count for
the data area. You need it to Sum instead. Click on any cell in the data
area, then right-click. Select Field Settings, then click on the Sum function.

Hope this helps,

Hutch

"Bemidji" wrote:

>
> Bemidji wrote:
> > Good afternoon,
> >
> > I am tracking downtime reason codes that have time associated with each
> > of them by date. I export data from Microsoft Infopath to a
> > spreadsheet, and then need to set up a spreadsheet that sums common
> > downtime occurrences by date. Here is an example of what is exported
> > to a spreasheet:
> >
> > A B
> > C
> >
> > 1 08-01-06 Warm-Up
> > 15
> > 2 08-01-06 Tooling
> > 60
> > 3 08-02-06 Insert
> > 5
> > 4 08-02-06 Breaks
> > 15
> > 5 08-02-06 Breaks
> > 30
> > 6 08-03-06 Waiting
> > 5
> > 7 08-03-06 Breaks
> > 15
> > 8 08-04-06 Lot Change
> > 16
> >
> > I want to now separate this data by date and reason codes into another
> > spreadsheet:
> >
> >
> > A B
> > C D
> > 08-01-06 08-02-06
> > 08-03-06 08-04-06
> >
> > 1 Breaks 0 45
> > 15 0
> > 2 Warm-Up 15 0
> > 0 0
> > 3 Tooling 60 0
> > 0 0
> > 4 Insert 0 5
> > 0 0
> > 5 Waiting 0 0
> > 5 0
> > 6 Lot Change 0 0
> > 0 16
> > 7
> > 8
> >
> > What would the formula be to accomplish this?
> >
> > Would the following get me on the right track? =if(b1:b8=Breaks, C
> > Value)????
> >
> > I want the 08-02-06 column to sum the occurrences of like types of
> > downtimes (15 + 30). I know I am missing something. Please help.
> > Thanks.
>
>
> I am assuming I would use the =SUMIF formula, but I am still having
> trouble separating by date. The layout of my post did not appear how I
> typed it, there is the date, downtime reason, and minutes down, 3
> separate columns, ABC.
>
>

Hi Tom,

I tried to create a pivot chart, and when I selected the desired
ranges, a dialogue box comes up that states "Reference is not valid".
I selected the date range, separated by a comma, the downtime reason,
separated by a comma, and then the minutes down. Is there something I
am doing wrong?

Thanks.

Bemidji


Tom Hutchins wrote:
> A pivot table may be your best option. Click any cell in your data, then
> select Data >> PivotTable and PivotChart Report. Make sure the correct range
> is selected by Excel and tell it to put the output on a new worksheet. Add
> the Downtime Reason to the Row Area, the Date to the Column Area, and the
> Minutes Down to the Data Area. By default, the pivot table will use Count for
> the data area. You need it to Sum instead. Click on any cell in the data
> area, then right-click. Select Field Settings, then click on the Sum function.
>
> Hope this helps,
>
> Hutch
>
> "Bemidji" wrote:
>
> >
> > Bemidji wrote:
> > > Good afternoon,
> > >
> > > I am tracking downtime reason codes that have time associated with each
> > > of them by date. I export data from Microsoft Infopath to a
> > > spreadsheet, and then need to set up a spreadsheet that sums common
> > > downtime occurrences by date. Here is an example of what is exported
> > > to a spreasheet:
> > >
> > > A B
> > > C
> > >
> > > 1 08-01-06 Warm-Up
> > > 15
> > > 2 08-01-06 Tooling
> > > 60
> > > 3 08-02-06 Insert
> > > 5
> > > 4 08-02-06 Breaks
> > > 15
> > > 5 08-02-06 Breaks
> > > 30
> > > 6 08-03-06 Waiting
> > > 5
> > > 7 08-03-06 Breaks
> > > 15
> > > 8 08-04-06 Lot Change
> > > 16
> > >
> > > I want to now separate this data by date and reason codes into another
> > > spreadsheet:
> > >
> > >
> > > A B
> > > C D
> > > 08-01-06 08-02-06
> > > 08-03-06 08-04-06
> > >
> > > 1 Breaks 0 45
> > > 15 0
> > > 2 Warm-Up 15 0
> > > 0 0
> > > 3 Tooling 60 0
> > > 0 0
> > > 4 Insert 0 5
> > > 0 0
> > > 5 Waiting 0 0
> > > 5 0
> > > 6 Lot Change 0 0
> > > 0 16
> > > 7
> > > 8
> > >
> > > What would the formula be to accomplish this?
> > >
> > > Would the following get me on the right track? =if(b1:b8=Breaks, C
> > > Value)????
> > >
> > > I want the 08-02-06 column to sum the occurrences of like types of
> > > downtimes (15 + 30). I know I am missing something. Please help.
> > > Thanks.
> >
> >
> > I am assuming I would use the =SUMIF formula, but I am still having
> > trouble separating by date. The layout of my post did not appear how I
> > typed it, there is the date, downtime reason, and minutes down, 3
> > separate columns, ABC.
> >

You need to create a pivot table, not a pivot chart. All your data is
included in one range for the pivot table. If you begin with the active cell
already in your data, Excel will make its best guess as to what that data
range is. Usually it finds the correct range. If it's correct, click Next; if
not correct, enter the range you want, then click Next.

Also, pivot tables work best when you have row and column headings:

A B C
W 1 2 3
X 4 5 6
Y 7 8 9
Z 10 11 12

Give it another try. Regards,

Hutch

"Bemidji" wrote:

> Hi Tom,
>
> I tried to create a pivot chart, and when I selected the desired
> ranges, a dialogue box comes up that states "Reference is not valid".
> I selected the date range, separated by a comma, the downtime reason,
> separated by a comma, and then the minutes down. Is there something I
> am doing wrong?
>
> Thanks.
>
> Bemidji
>
>
> Tom Hutchins wrote:
> > A pivot table may be your best option. Click any cell in your data, then
> > select Data >> PivotTable and PivotChart Report. Make sure the correct range
> > is selected by Excel and tell it to put the output on a new worksheet. Add
> > the Downtime Reason to the Row Area, the Date to the Column Area, and the
> > Minutes Down to the Data Area. By default, the pivot table will use Count for
> > the data area. You need it to Sum instead. Click on any cell in the data
> > area, then right-click. Select Field Settings, then click on the Sum function.
> >
> > Hope this helps,
> >
> > Hutch
> >
> > "Bemidji" wrote:
> >
> > >
> > > Bemidji wrote:
> > > > Good afternoon,
> > > >
> > > > I am tracking downtime reason codes that have time associated with each
> > > > of them by date. I export data from Microsoft Infopath to a
> > > > spreadsheet, and then need to set up a spreadsheet that sums common
> > > > downtime occurrences by date. Here is an example of what is exported
> > > > to a spreasheet:
> > > >
> > > > A B
> > > > C
> > > >
> > > > 1 08-01-06 Warm-Up
> > > > 15
> > > > 2 08-01-06 Tooling
> > > > 60
> > > > 3 08-02-06 Insert
> > > > 5
> > > > 4 08-02-06 Breaks
> > > > 15
> > > > 5 08-02-06 Breaks
> > > > 30
> > > > 6 08-03-06 Waiting
> > > > 5
> > > > 7 08-03-06 Breaks
> > > > 15
> > > > 8 08-04-06 Lot Change
> > > > 16
> > > >
> > > > I want to now separate this data by date and reason codes into another
> > > > spreadsheet:
> > > >
> > > >
> > > > A B
> > > > C D
> > > > 08-01-06 08-02-06
> > > > 08-03-06 08-04-06
> > > >
> > > > 1 Breaks 0 45
> > > > 15 0
> > > > 2 Warm-Up 15 0
> > > > 0 0
> > > > 3 Tooling 60 0
> > > > 0 0
> > > > 4 Insert 0 5
> > > > 0 0
> > > > 5 Waiting 0 0
> > > > 5 0
> > > > 6 Lot Change 0 0
> > > > 0 16
> > > > 7
> > > > 8
> > > >
> > > > What would the formula be to accomplish this?
> > > >
> > > > Would the following get me on the right track? =if(b1:b8=Breaks, C
> > > > Value)????
> > > >
> > > > I want the 08-02-06 column to sum the occurrences of like types of
> > > > downtimes (15 + 30). I know I am missing something. Please help.
> > > > Thanks.
> > >
> > >
> > > I am assuming I would use the =SUMIF formula, but I am still having
> > > trouble separating by date. The layout of my post did not appear how I
> > > typed it, there is the date, downtime reason, and minutes down, 3
> > > separate columns, ABC.
> > >
>
>

That appears to have worked. Thank you for your help!!


Tom Hutchins wrote:
> You need to create a pivot table, not a pivot chart. All your data is
> included in one range for the pivot table. If you begin with the active cell
> already in your data, Excel will make its best guess as to what that data
> range is. Usually it finds the correct range. If it's correct, click Next; if
> not correct, enter the range you want, then click Next.
>
> Also, pivot tables work best when you have row and column headings:
>
> A B C
> W 1 2 3
> X 4 5 6
> Y 7 8 9
> Z 10 11 12
>
> Give it another try. Regards,
>
> Hutch
>
> "Bemidji" wrote:
>
> > Hi Tom,
> >
> > I tried to create a pivot chart, and when I selected the desired
> > ranges, a dialogue box comes up that states "Reference is not valid".
> > I selected the date range, separated by a comma, the downtime reason,
> > separated by a comma, and then the minutes down. Is there something I
> > am doing wrong?
> >
> > Thanks.
> >
> > Bemidji
> >
> >
> > Tom Hutchins wrote:
> > > A pivot table may be your best option. Click any cell in your data, then
> > > select Data >> PivotTable and PivotChart Report. Make sure the correct range
> > > is selected by Excel and tell it to put the output on a new worksheet. Add
> > > the Downtime Reason to the Row Area, the Date to the Column Area, and the
> > > Minutes Down to the Data Area. By default, the pivot table will use Count for
> > > the data area. You need it to Sum instead. Click on any cell in the data
> > > area, then right-click. Select Field Settings, then click on the Sum function.
> > >
> > > Hope this helps,
> > >
> > > Hutch
> > >
> > > "Bemidji" wrote:
> > >
> > > >
> > > > Bemidji wrote:
> > > > > Good afternoon,
> > > > >
> > > > > I am tracking downtime reason codes that have time associated with each
> > > > > of them by date. I export data from Microsoft Infopath to a
> > > > > spreadsheet, and then need to set up a spreadsheet that sums common
> > > > > downtime occurrences by date. Here is an example of what is exported
> > > > > to a spreasheet:
> > > > >
> > > > > A B
> > > > > C
> > > > >
> > > > > 1 08-01-06 Warm-Up
> > > > > 15
> > > > > 2 08-01-06 Tooling
> > > > > 60
> > > > > 3 08-02-06 Insert
> > > > > 5
> > > > > 4 08-02-06 Breaks
> > > > > 15
> > > > > 5 08-02-06 Breaks
> > > > > 30
> > > > > 6 08-03-06 Waiting
> > > > > 5
> > > > > 7 08-03-06 Breaks
> > > > > 15
> > > > > 8 08-04-06 Lot Change
> > > > > 16
> > > > >
> > > > > I want to now separate this data by date and reason codes into another
> > > > > spreadsheet:
> > > > >
> > > > >
> > > > > A B
> > > > > C D
> > > > > 08-01-06 08-02-06
> > > > > 08-03-06 08-04-06
> > > > >
> > > > > 1 Breaks 0 45
> > > > > 15 0
> > > > > 2 Warm-Up 15 0
> > > > > 0 0
> > > > > 3 Tooling 60 0
> > > > > 0 0
> > > > > 4 Insert 0 5
> > > > > 0 0
> > > > > 5 Waiting 0 0
> > > > > 5 0
> > > > > 6 Lot Change 0 0
> > > > > 0 16
> > > > > 7
> > > > > 8
> > > > >
> > > > > What would the formula be to accomplish this?
> > > > >
> > > > > Would the following get me on the right track? =if(b1:b8=Breaks, C
> > > > > Value)????
> > > > >
> > > > > I want the 08-02-06 column to sum the occurrences of like types of
> > > > > downtimes (15 + 30). I know I am missing something. Please help.
> > > > > Thanks.
> > > >
> > > >
> > > > I am assuming I would use the =SUMIF formula, but I am still having
> > > > trouble separating by date. The layout of my post did not appear how I
> > > > typed it, there is the date, downtime reason, and minutes down, 3
> > > > separate columns, ABC.
> > > >
> >
> >