Results 1 to 8 of 8

20080823, 19:36 #1
 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, francisHope 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

20080823, 22:16 #2
 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?

20080823, 22:31 #3
 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.

20080824, 03:31 #4
 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, francisHope 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

20080824, 09:04 #5
 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.

20080826, 05:05 #6
 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>

20080826, 05:10 #7
 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.

20080828, 04:33 #8
 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, francisHope 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