NameSilo

need minor Excel help

Located in The Break Room started by verbster, Jan 2, 2012.

Replies:
7
Views:
1,046

  1. verbster

    verbster Blue Moose VIP ★★★★★★★★★★

    Posts:
    4,831
    Likes Received:
    3,642
    Have three columns in Excel 2010.

    june 22 $1.22
    june 34 $0.89
    june 45 $2.22
    july 12 $3.33
    july 34 $1.65
    july 45 $0.99

    How do I consolidate/merge to just make it
    june 101 $4.33
    july 91 $5.97

    Thanks.
     

    Attached Files:

    The views expressed on this page by users and staff are their own, not those of NamePros.
  2. Jawed

    Jawed Senior Member VIP Trusted Contest Holder ★★★★★★★★★★

    Posts:
    2,433
    Likes Received:
    587
    Create a 4th column, sub-total June and then July.
     
  3. verbster

    verbster Blue Moose VIP ★★★★★★★★★★

    Posts:
    4,831
    Likes Received:
    3,642
    Thanks. That works. However, I should have mentioned I have around 2000 entries in the three colums, with at least half like the example. Using the subtotal, it appears I would have to enter the formula parameters for each set of alike names in column A?

    I tink just highlighting and clicking autoSum will probably work for now. Not the most elegant solution, but....
     
    Last edited: Jan 2, 2012
  4. DU

    DU Secret Santa VIP ★★★★★★★★★★

    Posts:
    12,638
    Likes Received:
    19,292
    I have 2007

    Just highlight the all three columns and hit the "sub-total" button (On the DATA tab)

    Then you have collapse boxes on the left to hide/show detail.
     
  5. verbster

    verbster Blue Moose VIP ★★★★★★★★★★

    Posts:
    4,831
    Likes Received:
    3,642
    What you described seems simple enough, but I must be making a basic mistake somewhere. The subtotal doesn't seem to tally the two adjacent cells. And it inserts two lines in the original table. I've attached a couple of screen shots.

    I thought maybe the "consolidate" feature (using "sum") would work, but I can't seem to get the "reference" field right. The description seems right, but maybe I'm thinking wrong.
     

    Attached Files:

  6. DU

    DU Secret Santa VIP ★★★★★★★★★★

    Posts:
    12,638
    Likes Received:
    19,292
    Select both rows you want to subtotal in the subtotal options.

    There's an issue with it entering subtotals in the row?

    You can use the outliner on the left (Plus / Minus buttons... or click 1,2,3- to collapse to show subtotals only, grand totals, or detail.

    Here's the first link I found on google:

    http://chandoo.org/wp/2010/02/09/subtotal-formula-excel/

    Shows how to use it.
     
  7. verbster

    verbster Blue Moose VIP ★★★★★★★★★★

    Posts:
    4,831
    Likes Received:
    3,642
    It almost works, but for some reason it doesn't total in the top number. For example, if column B had

    10
    15
    20
    25

    it shows the subtotal as 60 instead of 70....just leaves the 10 out of the sum. Does that in both columns.
     
  8. DU

    DU Secret Santa VIP ★★★★★★★★★★

    Posts:
    12,638
    Likes Received:
    19,292
    Top row show should be header/column fields. That makes the dialog box make more sense too :)
     

Want to reply or ask your own question?

It only takes a minute to sign up – and it's free!
NameWorth
  1. NamePros uses cookies and similar technologies. By using this site, you are agreeing to our privacy policy, terms, and use of cookies.
    Dismiss Notice
Loading...