formula question

G

Guest

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
 
G

Guest

Hi Max, I am not apply you proposal foumal in our worksheet, is possable
direct e-mail my workour worksheet you looking for

Thanks
J
 
G

Guest

Hi Max
How about if I put the summary in worksheet 2 and the Column A and Column
B will be add for future.

Thanks
J
 
B

Bob Phillips

Jackie,

In B13, add

=SUMPRODUCT(--ISNUMBER(SEARCH(A13,$A$2:$A$10)),$B$2:$B$10)

and then copy down to B14

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top