If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
formula QUESTION
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 |
#3
|
|||
|
|||
formula QUESTION
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I have a question regarding countif formula. | Fahad Farid Ansari | Worksheet Functions | 6 | October 1st, 2005 11:57 PM |
Survey Results | SAm | Running & Setting Up Queries | 10 | May 17th, 2005 08:32 PM |
Formula Question | shadrach | General Discussion | 1 | May 10th, 2005 08:25 AM |
Question about Absolute reference formula | Maria | General Discussion | 3 | June 23rd, 2004 06:27 PM |
INDIRECT formula question | wardcanoe | Worksheet Functions | 2 | December 30th, 2003 11:31 PM |