Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing Group and insert Value in the next cells (Excel 2003)

    Hi

    In column A, I have names that appear as ABC 01, ABC 02,ABC 03, I want to sum
    the group ABC, XYZ or any other groups which appear in the column A and put
    a value in column B depending on the sum total figure. For example, if the total sum
    for ABC is more than 100, all the entries in the column B related to ABC, ie ABC 01, ABC 02, ABC 03.....so on, will have a "Y" in the respective rows, otherwise, "N" will be input in the related entries.

    thanks in advance

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Summing Group and insert Value in the next cells (Excel 2003)

    Are ALL values of the form "CCC nn", i.e.
    - Will the names always be 3 characters long?
    - Will the numbers always consist of 2 digits?
    - Will there always be a space between them?

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

    Re: Summing Group and insert Value in the next cells (Excel 2003)

    If the conditions of my previous reply hold, you can use the following array formula in B1 (confirm with Ctrl+Shift+Enter):

    =IF(SUM(IF(LEFT($A$1:$A$100,3)=LEFT(A1,3),1*MID($A $1:$A$100,5,2)))>100,"Y","N")

    Adjust the range A1:A100 as needed.
    You can fill down the formula.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Group and insert Value in the next cells (Excel 2003)

    Hi Hans

    Thanks. I wasn't online at the time you asked the question. The names are not always 3 characters long,
    it varies but its always have the group name in front before the sub name, such as Hans 01, HanV 00001 or Hans@Netherland 01

    Thanks

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Summing Group and insert Value in the next cells (Excel 2003)

    You could use a series of intermediate formulas - see the attached workbook.
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Group and insert Value in the next cel

    and with totals by group <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Attached Files Attached Files

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

    Re: Summing Group and insert Value in the next cel

    Thanks, I hope it'll be useful to Franciz.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Group and insert Value in the next cel

    Hi Hans and Servando

    This is extremely useful, I learn more when I come here. Thanks.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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