formula QUESTION

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

[Max]

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
--
<[email protected]> wrote in message news:[email protected]...
> 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
>
>
>

[Ron Rosenfeld]

On Thu, 10 Nov 2005 15:16:56 +0800, <[email protected]> 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