formula question

[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

 

[Max]

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

 

[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

 

[Max]

Could you upload a small sample copy of your file,
and then post a *link* to it here in reply ?

One free filehost you could use to upload:
http://www.flypicture.com/

Note: Pl do not post any attachments direct to the newsgroup

 

[Guest]

Hi Max
I have upload to your post drive and this this for your online view our
excel sapmle. http://www.flypicture.com?display=updone&id=rd32k6/d

My solution is Summany Part

The sales value is sum of ABC and all ABC country company

Please advice how to apply the forumal in this worksheet.

Thanks
Jackie Wong

 

[Max]

Put in B13, copy down:
=SUMPRODUCT(--ISNUMBER(SEARCH(A13,$A$2:$A$8)),$B$2:$B$8)

 

[Max]

In case needed, here's the implemented sample:
http://cjoint.com/?lkjSkwVUWk

 

[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

 

[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)

 

[Max]

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

This is actually by far the better set-up <g>
Sample at: http://cjoint.com/?lklaxpzd1p

So Sheet1 houses the source table in cols A and B, data from row2 down

Company Sales Value HK$
ABC HK 100000
ABC JAPAN 100000
ABC CHINA 100000
ABC TAIWAN 20000
BCE HK 30000
BCE JAPAN 3000
BCE TAIWAN 200
etc

And in another sheet, named: Summary
it would be set-up in cols A and B as:
(with the companies listed in A2 down)

Company Total Sales Value
ABC
BCE
etc

Put in B2, copy down:

=SUMPRODUCT(--ISNUMBER(SEARCH(A2,Sheet1!$A$2:$A$100)),Sheet1!$B$2:$B$100)

Adapt the 2 ranges in the formula (below)
to suit the expected max extent of data in Sheet1:

Sheet1!$A$2:$A$100
Sheet1!$B$2:$B$100

 

[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

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