Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    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?

  2. #2
    Bronze Lounger
    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 that-my 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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  4. #4
    Bronze Lounger
    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

  5. #5
    Bronze Lounger
    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 forgot-I would like the monthly summaries to list each "1" and each "2", and all their pertinent detail (i. e., in columns B through G-for example, cost per unit, total cost, total number of units purchased, etc.) in addition to counting them.
    Thanks,
    Jeff

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting Items On Different Worksheets (2000)

    <P ID="edit" class=small>Edited by HansV on 26-Jul-02 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.

  8. #8
    Bronze Lounger
    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Items On Different Worksheets (2000)

    No problem here-but 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!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 time-consuming, you might consider hiring a professional to design a spreadsheet for you.

    Regards,
    Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •