Dear
We have a excel file two column, one is word , one is value. eg as below.

Column 1 Column 2
A 1
A1 2
A2 3
A3 4
B 5
B1 6
B3 7

I want formula is

new value is column 3 = all column 1 relate word A (eg A1, A2.....) and
column 2 number with sum in a total number.

eg. ( if related word A field ( A + A1 + A2 + A3 ) ) = 10

I am not sure which forumual is better using in this workheet, please
advice.

Thanks
Jackie wong

One response given in microsoft.public.excel reads:
(Please do not multi-post)

eg. ( if related word A field ( A + A1 + A2 + A3 ) ) = 10

Assuming source data is in A1:B100
text in col A, numbers in col B

List the text (letter/word) in C1 down, eg:

In C1: A
In C2: B
etc

Then we could put in D1, and copy down:
=SUMPRODUCT(--ISNUMBER(SEARCH(C1,$A$1:$A$100)),$B$1:$B$100)

Col D will return the required sums

If you need it to be case-sensitive,
replace SEARCH with FIND in the formula
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
wrote in message ...
Dear
We have a excel file two column, one is word , one is value. eg as
below.

Column 1 Column 2
A 1
A1 2
A2 3
A3 4
B 5
B1 6
B3 7

I want formula is

new value is column 3 = all column 1 relate word A (eg A1, A2.....) and
column 2 number with sum in a total number.

eg. ( if related word A field ( A + A1 + A2 + A3 ) ) = 10

I am not sure which forumual is better using in this workheet, please
advice.

Thanks
Jackie wong

On Thu, 10 Nov 2005 15:16:56 +0800, wrote:

Dear
We have a excel file two column, one is word , one is value. eg as below.

Column 1 Column 2
A 1
A1 2
A2 3
A3 4
B 5
B1 6
B3 7

I want formula is

new value is column 3 = all column 1 relate word A (eg A1, A2.....) and
column 2 number with sum in a total number.

eg. ( if related word A field ( A + A1 + A2 + A3 ) ) = 10

I am not sure which forumual is better using in this workheet, please
advice.

Thanks
Jackie wong



If I understand you correctly, you want to add all the numbers in column 2 that
have, in Column 1, a label that begins with the letter "A".

If that is the case, this will work:

=SUMIF(A:A,"A*",B:B)

If you want to put the label portion in, for example, C1, then this variation
is the equivalent:

=SUMIF($A:$A,"="&C1&"*",$B:$B)

With an 'A' in C1, the result will be 10.
With a 'B' in C1, the result will be 18


--ron