Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Create category totals (any)

    Probably an easy one?

    In one column (say C) I have a number of monetary amounts (say from C8:C101)
    In the next column (D) I have a small number of codes (say about five), which is the "category" of the amount in the cell to its left. Say the text is "Type A" .. "Type E", for example.

    I want to find the total of the amounts for "Type A", and for "Type B"... and for "Type E", and put the sums in cells C103:C107 (say) with the text "TypeA" ... "Type E" in cells D103-D107 (say).

    I'm sure I did this sort of thing years and years ago in Supercalc 2 on an Amstrad PCW8512 (don't ask!), and suspect one of the Lookup functions could be involved!

    Can someone give me a few instant formulae? Many thanks!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

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

    Re: Create category totals (any)

    In cell C103, enter this formula:

    =SUMIF($D$8:$D$101,D103,$C$8:$C$101)

    Fill down to cell C107.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create category totals (any)

    Hans

    Thanks! I think I understand what the formula means!

    I cut and pasted from your message, and for some reason it seemed to have changed the commas to semicolons. With that corrected it gives exactly what I want. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    The international super-hero strikes again (and I should go on an Excel course...) !

    John
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

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

    Re: Create category totals (any)

    As usual, I originally forgot to change the semicolons (the list separator in my Dutch-language system) to commas. I noticed this while posting, and immediately corrected it, but you saw the original version.

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create category totals (any)

    Hans

    Seems like a good idea to change the Dutch convention for list separators?! See to it, would you? Thanks! <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

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

    Re: Create category totals (any)

    Uh, yeah. While I'm at it, I will ask Mr Tony Blair to implement the following in the UK:
    - Switch entirely to metric.
    - Switch from sterling to euros.
    - Drive on the right.
    OK with you? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create category totals (any)

    Yes, fine! But you first...
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

Posting Permissions

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