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

Topic

New Reply

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

Replies

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

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

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

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

Reply | Quote

and with totals by group

Reply | Quote

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

Reply | Quote

Hi Hans and Servando

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

cheers, francis

Reply | Quote