'Automatic' counting

[SimonG]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

Hi,

I am trying to have excel count data for me. A number of columns are present with the data and I would like Excel to find each time a code (this denotes a type of hazardous waste) appears (in one column) and then count the amount of waste that was consigned (another column) so that at the end of the 20 or so codes present, I have a total for each waste stream. Data is organised on a financial quarterly basis, one quarter per sheet, so I can then compare trends between a number of quarters.

How can I do this please?

Thanks.

 

[Mike Middleton]

SimonG -

For each sheet (quarter), you could use the SUMIF worksheet function or a
Pivot Table.

If you organized your data in standard database form with a column for type,
amount, quarter, and year with all data on a single sheet, you could use a
pivot table and pivot chart to show the trend.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[SimonG]

Hi Mike,

Many thanks for the answer. I tried the pivot table option and found that I get a count of the number of occurrences of the waste code instead of a count of the values themselves. I am clearly doing something wrong!

I am using the EWC code (waste code identifier) on the far left column and data, middle of the table, is the weight column. So, the data section is the count of times the number of EWC code appears.

Would appreciate an indication of what I am doing wrong!

Thank you.

 

[Mike Middleton]

SimonG -

In the Layout when you create the pivot table, drag and drop Code to the Row
area, and drag and drop Weight to the Data area. After dropping Weight, if
it doesn't say Sum of Weight, double-click it, e.g., double-click the "Count
of Weight" button, and change it to "Sum."

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[SimonG]

Mike,

That was brilliant, thank you. It worked a treat but I am sorry to say that I have another question.

With a number of spreadsheets with pivoted data, there are subtle differences in the codes present in each quarter. Is there a way for Excel to go through each sheet and pull out the results from the pivot tables and populate one table with columns denoting quater and rows denoting the amount of each code present per quarter?

I am sorry to keep asking questions.

Simon

 

[Mike Middleton]

SimonG -

For "subtle differences in the codes," I don't think Excel can handle the
"subtle" problem. A different code is a different code.

I still recommend: "If you organized your data in standard database form
with a column for type, amount, quarter, and year with all data on a single
sheet, you could use a pivot table and pivot chart to show the trend." For
example, a two-way pivot table could show codes in a column on the left,
quarter&year in a row across the top, and sum of weights in the body of the
table.

If for some reason you must continue to use multiple databases on multiple
sheets instead of a single database on a single sheet, creating the overall
summary is beyond my very basic experience with pivot tables.

If you don't get assistance here in microsoft.public.mac.office.excel, you
could post in microsoft.public.excel.misc (in which case it's important to
specify "Mac Excel 2008" because I think there may be some differences in
pivot tables between Mac and Windows versions).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[SimonG]

Hi Mike,

With a bit more playing with the table it worked beautifully! Thank you.

Simon