Quick question for Excel experts

Discussion in 'After Hours Lounge (Off Topic)' started by Brian Perry, Apr 28, 2004.

  1. Brian Perry

    Brian Perry Cinematographer

    Joined:
    May 6, 1999
    Messages:
    2,807
    Likes Received:
    0
    I have a speadsheet that includes multiple rows with redundant data that I need to consolidate into summary lines. For example, let's say I have the following ten rows (in three columns):

    20 March Soybeans
    25 March Soybeans
    15 May Soybeans
    93 May Soybeans
    42 July Corn
    11 July Corn
    32 July Corn
    431 July Corn
    16 September Corn
    32 September Corn

    and I want those lines to consolidate into:

    45 March Soybeans
    108 May Soybeans
    516 July Corn
    48 September Corn

    or even:

    153 Soybeans
    564 Corn

    Can this be done without manually inserting a sum function between each different month and/or commodity? I'm used to creating AS/400 queries where stuff like this is a breeze but now I'm being given the data in spreadsheet format and I'm having a tough time producing the same results. Someone I know mentioned a "pivot table" function in Excel, but I tried it and it didn't quite give me the results I need (the data I'm working with has many more rows and columns than the example above so the pivot table was unwieldy--we're talking about thousands of rows).

    Thanks!
     
  2. chris_everett

    chris_everett Second Unit

    Joined:
    Jul 20, 2003
    Messages:
    403
    Likes Received:
    0
    Look at the DSUM function. The help in excel is quite good for all of the formulas, but that should get you started.
     
  3. Todd Henry

    Todd Henry Second Unit

    Joined:
    Feb 4, 2002
    Messages:
    324
    Likes Received:
    0
    You can also use the subtotals function under the Data menu if you data is sorted by month so all of March is grouped together.

    Todd
     
  4. Rob Gillespie

    Rob Gillespie Producer

    Joined:
    Aug 17, 1998
    Messages:
    3,632
    Likes Received:
    5
    Pivot Table.

    (edit)

    Sorry, didn't see you mention PTs before. As Alex says below they're probably the most straightforward way of doing what you want.
     
  5. Alex Spindler

    Alex Spindler Producer

    Joined:
    Jan 23, 2000
    Messages:
    3,971
    Likes Received:
    0
    Yeah, I was going to say if you have specific sets of criteria you're after, the very fast way of doing it is applying an Autofilter so you have drop down criteria for every row. Then add the following function somewhere off to the right on Row 1:

    =SUBTOTAL(9,A:A)

    Assuming A is the column you want to sum, you just have to choose the right criteria for the drop downs for what you're after and record the information separately.

    If you have a series of criteria you're after repeatedly (like if this is a weekly report and they need tons of different result values), you may just want to build a set of SUMIF or DSUM calculations and build from there.

    I'm still not sure why the Pivot Table isn't good for you, as you can just select the columns that you want and they create just about any sum combination you could be after.
     
  6. Denward

    Denward Supporting Actor

    Joined:
    Feb 26, 2001
    Messages:
    552
    Likes Received:
    0
    Set up your pivot table with MONTH in the columns and CROP in the rows and the QUANTITY in the body of the pivot table. You'll end up with a 12 column table with a row for each crop and totals for each row and column. That seems like the perfect way to summarize your info. Pivot tables are very good at summarizing thousands of rows of data.
     
  7. Dave Poehlman

    Dave Poehlman Producer

    Joined:
    Mar 8, 2000
    Messages:
    3,813
    Likes Received:
    0
    I just want to say that I love Excel. It is IMO the most versatile piece of software ever created.


    Word blows.
     
  8. Alex Spindler

    Alex Spindler Producer

    Joined:
    Jan 23, 2000
    Messages:
    3,971
    Likes Received:
    0
    You want to know the most depressing thing ever? Try recreating even the most basic Excel formulas in Access. Both Microsoft, but one has the formula versatility of an amoeba by comparison.
     
  9. Dave Poehlman

    Dave Poehlman Producer

    Joined:
    Mar 8, 2000
    Messages:
    3,813
    Likes Received:
    0
    Oh I use Excel for everything... making signs for the office, football pools, using its visual basic macro capabilities I can make it talk with other applications..pulling data, manipulating it, puking it back... it's awesome.

    Word still blows.
     

Share This Page