Results 1 to 11 of 11

20020726, 11:38 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Sorting Items On Different Worksheets (2000)
I know this must be a simple technique, but I can't seem to find anything on it: I would like to sort items on several worksheets to another worksheet. For example, assume I have a file with 12 worksheets. Each of the worksheets correspond to a month in a calendar year. In each, I have itemized purchases of inventory of 2 types , i and 2. As a result, in each cell in column A of each worksheet there appears either a 1 or a 2, with the remainder of the cells in the respective row relating to other characteristics of the item purchased.
What I would like to do is total the 1s and 2s, by month on a "Summary" worksheet.
Any clues?

20020726, 12:08 #2
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting Items On Different Worksheets (2000)
Hans,
Sorry about thatmy mind was ahead of my typing.
Ideally, what I want to do is to total, by month, on a completely separate worksheet, the total number of items categorized as a "1" and as a "2". I suppose after I have done that, I can then total them, again by "1s" and "2s", for the whole year.
Thanks, and I hope this is clearer.
Jeff

20020726, 12:49 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sorting Items On Different Worksheets (2000)
OK, that shouldn't be too hard. You can use the COUNTIF function for this.
On your summary sheet, create a table like the following:
<table border=1><td align=right>Month</td><td align=right>Total of 1</td><td align=right>Total of 2</td><td align=right></td><td align=right></td><td align=right></td><td align=right>Jan</td><td align=right>6</td><td align=right>2</td><td align=right>Feb</td><td align=right>3</td><td align=right>4</td><td align=right>Mar</td><td align=right>5</td><td align=right>5</td><td align=right>Apr</td><td align=right>6</td><td align=right>8</td><td align=right>May</td><td align=right>7</td><td align=right>2</td><td align=right>Jun</td><td align=right>3</td><td align=right>5</td><td align=right>Jul</td><td align=right>4</td><td align=right>7</td><td align=right>Aug</td><td align=right>2</td><td align=right>3</td><td align=right>Sep</td><td align=right>5</td><td align=right>6</td><td align=right>Oct</td><td align=right>6</td><td align=right>8</td><td align=right>Nov</td><td align=right>3</td><td align=right>4</td><td align=right>Dec</td><td align=right>5</td><td align=right>2</td><td align=right></td><td align=right></td><td align=right></td><td align=right>Total</td><td align=right>55</td><td align=right>56</td></table>
In the cell with the Total of 1 for January, the formula is
=COUNTIF(SheetForJan!A:A,1)
where SheetForJan is the name of the sheet with the data for January. Analogously for the Total of 2 and for the other months.
The formulas in the bottom row are standard SUM formulas.
HTH,
Hans

20020726, 12:55 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting Items On Different Worksheets (2000)
Hans,
Good job. Now, what if I want to compute an average price for the items per month and year. That is, assume in column B of each monthly worksheet I have the cost of each unit. Hoe can I carry that forward to the summary sheet, and compute an average cost for each "1" and "2" per month, and yearly.
Jeff

20020726, 13:07 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting Items On Different Worksheets (2000)
Hans,
I almost forgotI would like the monthly summaries to list each "1" and each "2", and all their pertinent detail (i. e., in columns B through Gfor example, cost per unit, total cost, total number of units purchased, etc.) in addition to counting them.
Thanks,
Jeff

20020726, 13:09 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sorting Items On Different Worksheets (2000)
It's not clear to me what you want. You start by saying that you want to sort items. At the end you say that you want to total the 1s and 2s.
Do you want to count the number of 1s and 2s by month? Or do you want to sum other data by month, with separate results for 1s and for 2s? Or something else?

20020726, 13:37 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sorting Items On Different Worksheets (2000)
<P ID="edit" class=small>Edited by HansV on 26Jul02 16:37.</P>Now you tell me!
There is also a function SUMIF, analogous to COUNTIF. You can use this to sum data:
=SUMIF(SheetForJan!A:A,2,SheetForJan!C:C) will sum all entries in column C on SheetForJan for which the corresponding entry in column A is 2.
=SUMIF(SheetForJan!A:A,2,SheetForJan!B:[img]/forums/images/smilies/cool.gif[/img]/COUNTIF(SheetForJan!A:A,2) will compute the average of the entries in column B for which the corresponding entry in column A is 2 (originally I wrote 1, a stupid typo  sorry), by dividing the sum by the number of entries.
If this gets too complicated, I suggest that you look up the database functions like DCOUNT, DSUM and DAVERAGE in the online help.

20020726, 13:50 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting Items On Different Worksheets (2000)
Hans,
I think this will work great. Thanks again.
Just one more question: what do I do to list the individual purchases by month by category (i. e., 1s and 2s) on a separate worksheet?
Jeff

20020726, 13:58 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sorting Items On Different Worksheets (2000)
Jeff,
You may not want to hear this. If you want to do all these kinds of aggregation etc., it would probably be easier to reorganise your data into one data sheet and one summary sheet.
If you place the data in one sheet (with  if necessary  an extra column for the date or month), it becomes much easier to analyze the data for a whole year. You can use subtotals (by month and by category), pivot tables, filters, ...
It'll take a bit of time to change your setup, but I think this effort will repay itself in the end.
Regards,
Hans

20020726, 14:01 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sorting Items On Different Worksheets (2000)
No problem herebut only if you wouldn't mind helping me. I do not use Access, and am not familiar with pivot tables. But, if you could help, I wouldn't mind tackling the project at all!

20020726, 14:19 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sorting Items On Different Worksheets (2000)
Jeff,
I'm sorry, your request is too broad.
The Lounge can help you with specific questions, but it can't develop your software solutions for you. It is not a free consultancy agency.
I would suggest that you do a bit of studying yourself. Make a copy of an existing table in Excel, or create a small table for experiments, and look at the various options in the Data menu. In particular, just try out the AutoFilter and SubTotals. It shouldn't be too hard to find out what they do by experimenting. You can always resort to the online help. And if things go wrong, there is the Undo option.
If that's too difficult or too timeconsuming, you might consider hiring a professional to design a spreadsheet for you.
Regards,
Hans