Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Using Excel 2003
I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Use a Pivot Table
-- Gary''s Student - gsnu200816 "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Is there a column for the dept number?
Try something like this. Assuming column A is the dept number. =SUMPRODUCT(--(A1:A100=3001),--(MONTH(D1:D100)=1),--(H1:H100=1)) That will count entries for dept 3001 for the month of January with error code 1. Better to use cells to hold the criteria: N1 = dept number = 3001 O1 = month number = 1 (months 1-12) P1 = error level code = 1 =SUMPRODUCT(--(A1:A100=N1),--(MONTH(D1:D100)=O1),--(H1:H100=P1)) -- Biff Microsoft Excel MVP "Karen" wrote in message ... Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
"Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Hi,
1. Select all the data with one row of titles and choose Data, PivotTable and PivotChart Report 2. Click Next twice 3. Click Layout and drag the Department field button to the Row area 4. Drag the Level field button to the Row area 5. Drag the Date button to the Row area 6. Drag the Level field button to the Data area If the Level button in the Data area says Sum of Level then double click it and change the summary calculation to Count and click OK. 7. Click OK, Finish. 8. Place your mouse in the Date field and choose PivotTable, Group & Show Detail, Group 9. Leave Months selected and click OK If this helps, please click the Yes button Cheers, Shane Devenshire "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Thank you for your help!
"T. Valko" wrote: Is there a column for the dept number? Try something like this. Assuming column A is the dept number. =SUMPRODUCT(--(A1:A100=3001),--(MONTH(D1:D100)=1),--(H1:H100=1)) That will count entries for dept 3001 for the month of January with error code 1. Better to use cells to hold the criteria: N1 = dept number = 3001 O1 = month number = 1 (months 1-12) P1 = error level code = 1 =SUMPRODUCT(--(A1:A100=N1),--(MONTH(D1:D100)=O1),--(H1:H100=P1)) -- Biff Microsoft Excel MVP "Karen" wrote in message ... Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Thank you!
"Gary''s Student" wrote: Use a Pivot Table -- Gary''s Student - gsnu200816 "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Thank you for your help!
It was just what I needed! "Shane Devenshire" wrote: Hi, 1. Select all the data with one row of titles and choose Data, PivotTable and PivotChart Report 2. Click Next twice 3. Click Layout and drag the Department field button to the Row area 4. Drag the Level field button to the Row area 5. Drag the Date button to the Row area 6. Drag the Level field button to the Data area If the Level button in the Data area says Sum of Level then double click it and change the summary calculation to Count and click OK. 7. Click OK, Finish. 8. Place your mouse in the Date field and choose PivotTable, Group & Show Detail, Group 9. Leave Months selected and click OK If this helps, please click the Yes button Cheers, Shane Devenshire "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
I forgot to ask...
When I set this up, I selected all the current data. I'm going to adding rows to this speadsheet. Should I have selected several blank rows below the cells that contain dataso the pivot chart will be populated with the new data? Is there another way to do this? Thanks again, Karen "Shane Devenshire" wrote: Hi, 1. Select all the data with one row of titles and choose Data, PivotTable and PivotChart Report 2. Click Next twice 3. Click Layout and drag the Department field button to the Row area 4. Drag the Level field button to the Row area 5. Drag the Date button to the Row area 6. Drag the Level field button to the Data area If the Level button in the Data area says Sum of Level then double click it and change the summary calculation to Count and click OK. 7. Click OK, Finish. 8. Place your mouse in the Date field and choose PivotTable, Group & Show Detail, Group 9. Leave Months selected and click OK If this helps, please click the Yes button Cheers, Shane Devenshire "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Hi,
Select the range (with the headings) and press Ctrl+L. This will convert the range to a list. One of the features of a list is that it is auto expanding in nature. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Karen" wrote in message ... I forgot to ask... When I set this up, I selected all the current data. I'm going to adding rows to this speadsheet. Should I have selected several blank rows below the cells that contain dataso the pivot chart will be populated with the new data? Is there another way to do this? Thanks again, Karen "Shane Devenshire" wrote: Hi, 1. Select all the data with one row of titles and choose Data, PivotTable and PivotChart Report 2. Click Next twice 3. Click Layout and drag the Department field button to the Row area 4. Drag the Level field button to the Row area 5. Drag the Date button to the Row area 6. Drag the Level field button to the Data area If the Level button in the Data area says Sum of Level then double click it and change the summary calculation to Count and click OK. 7. Click OK, Finish. 8. Place your mouse in the Date field and choose PivotTable, Group & Show Detail, Group 9. Leave Months selected and click OK If this helps, please click the Yes button Cheers, Shane Devenshire "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Thank you for your help - I'm a bit confused. When I selected the range of
cells that contain data, I pressed Ctrl+L and a dialog box opened with a prompt "Where is the data for your list?" It gave an absolute range of what I just highlighted (=$A$1:$L$39). If I enter data in row 40, will my Pivot Table be populated? Thank you, Karen "Ashish Mathur" wrote: Hi, Select the range (with the headings) and press Ctrl+L. This will convert the range to a list. One of the features of a list is that it is auto expanding in nature. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Karen" wrote in message ... I forgot to ask... When I set this up, I selected all the current data. I'm going to adding rows to this speadsheet. Should I have selected several blank rows below the cells that contain dataso the pivot chart will be populated with the new data? Is there another way to do this? Thanks again, Karen "Shane Devenshire" wrote: Hi, 1. Select all the data with one row of titles and choose Data, PivotTable and PivotChart Report 2. Click Next twice 3. Click Layout and drag the Department field button to the Row area 4. Drag the Level field button to the Row area 5. Drag the Date button to the Row area 6. Drag the Level field button to the Data area If the Level button in the Data area says Sum of Level then double click it and change the summary calculation to Count and click OK. 7. Click OK, Finish. 8. Place your mouse in the Date field and choose PivotTable, Group & Show Detail, Group 9. Leave Months selected and click OK If this helps, please click the Yes button Cheers, Shane Devenshire "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possible Lookup
Thanks again for your help Shane. The instructions on what you showed me work
great. Although, I want to create a chart from the data. If the errors for that month are zero, I want the pivot table data and the chart to reflect that. Is there a way of tweaking the below instructions so that data will be shown? Thanks, Karen "Shane Devenshire" wrote: Hi, 1. Select all the data with one row of titles and choose Data, PivotTable and PivotChart Report 2. Click Next twice 3. Click Layout and drag the Department field button to the Row area 4. Drag the Level field button to the Row area 5. Drag the Date button to the Row area 6. Drag the Level field button to the Data area If the Level button in the Data area says Sum of Level then double click it and change the summary calculation to Count and click OK. 7. Click OK, Finish. 8. Place your mouse in the Date field and choose PivotTable, Group & Show Detail, Group 9. Leave Months selected and click OK If this helps, please click the Yes button Cheers, Shane Devenshire "Karen" wrote: Using Excel 2003 I have a spreadsheet that tracks errors for 3 departments. 3001 3002 3003 Not sure if you need ALL this info, but I'll give it to you if you do. The spreadsheet has 12 columns (A thru L) Column D (Date Discovered) contains the date I want to use to segregate the errors. Column H (Level) contains the number of the error level. There are 3 levels (Level 1,2, & 3) All I want to do is total the amount of errors by department, by level and by month. Should I use the Lookup function for something like this? If so, how do I go about this? Can someone lead me in the right direction? Thank you, Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |