Possible Lookup

[Karen]

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

 

[Gary''s Student]

Use a Pivot Table

 

[T. Valko]

Is there a column for the dept number?

Try something like this. Assuming column A is the dept number.

=SUMPRODUCT(--(A1:A100=3001),--(MONTH(D1100)=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(D1100)=O1),--(H1:H100=P1))

 

[Shane Devenshire]

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

 

[Shane Devenshire]

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]

Thank you for your help!

Is there a column for the dept number?

Try something like this. Assuming column A is the dept number.

=SUMPRODUCT(--(A1:A100=3001),--(MONTH(D1100)=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(D1100)=O1),--(H1:H100=P1))

 

[Karen]

Thank you!

Use a Pivot Table

 

[Karen]

Thank you for your help!
It was just what I needed!

 

[Karen]

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

 

[Ashish Mathur]

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]

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

 

[Karen]

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