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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Summing Group and insert Value in the next cells (Excel 2003)

    Author
    Topic
    WSfranciz
    AskWoody Lounger
    August 23, 2008 at 6:36 pm #453413

    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

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      August 23, 2008 at 9:16 pm #1122792

      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?

      Reply | Quote
    • WSHansV
      AskWoody Lounger
      August 23, 2008 at 9:31 pm #1122794

      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.

      Reply | Quote
      • WSfranciz
        AskWoody Lounger
        August 24, 2008 at 2:31 am #1122810

        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

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          August 24, 2008 at 8:04 am #1122839

          You could use a series of intermediate formulas – see the attached workbook.

          Reply | Quote
          • WSServando
            AskWoody Lounger
            August 26, 2008 at 4:05 am #1123176

            and with totals by group bow

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              August 26, 2008 at 4:10 am #1123177

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

              Reply | Quote
            • WSfranciz
              AskWoody Lounger
              August 28, 2008 at 3:33 am #1123539

              Hi Hans and Servando

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

              cheers, francis

              Reply | Quote
    Viewing 1 reply thread
    Reply To: Summing Group and insert Value in the next cells (Excel 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.