A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

formula QUESTION



 
 
Thread Tools Display Modes
  #1  
Old November 10th, 2005, 08:16 AM
external usenet poster
 
Posts: n/a
Default 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  
Old November 10th, 2005, 08:48 AM
Max
external usenet poster
 
Posts: n/a
Default 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  
Old November 10th, 2005, 01:18 PM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.