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

1. ## 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.

regards, francis

2. ## 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. ## 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. ## 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

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

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

6. ## 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>

7. ## Re: Summing Group and insert Value in the next cel

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

8. ## 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

#### Posting Permissions

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