Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
i am using open office version 3.0
i got three columns name date, description
and amount.i need sum of amount which contain
description of special word like "mobile".
means it may be mobile bill or mobile repair etc..
=SUM('file:///C:/privet/open
office/monthly09.ods'#$'JAN-
09'.B5:B10="oyster" ; 'file:///C:/privet/open
office/monthly09.ods'#$'JAN-09'.C5:C10)
I can't really offer anything else because I'm not sure what you are trying to do.
First, I assume you modified my suggestion for your actual filepath, range and criteria.
Second, you ask about summing based on the word "mobile" but the example formula you provided has the word "oyster" in it.
I'm really not sure what you are trying to do. Please provide an example of your data and what you want the result to be and maybe we can offer something else.
0 
yes sir, you are right i am sorry for that but. actually i need
that i want sum of amount witch contains spacial word. for
example.
Date Description Amounnt1/2/09 Mobile bill £50.00
4/2/09 Lycatel bill (mobile) £10.00
5/2/09 Rent £150.00
10/2/09 Top up (mobile) £15.00i need sum of amount which contain ward 'mobile' in
description. means here i want result is £75.
0
Works for me...this formula produces £75.00.
You have to make sure that your Amounts are formatted as Currency, or at least as numbers. If Excel interprets £50.00 etc. as text, the results of the formula will be 0.
=SUMIF(B2:B5,"=*mobile*",C2:C5)
A B C 1 Date Description Amount 2 1/2/2009 Mobile bill £50.00 3 4/2/2009 Lycatel bill (mobile) £10.00 4 5/2/2009 Rent £150.00 5 10/2/2009 Top up (mobile) £15.00
0
I don't know what else to say.
I copied the data from your post, put it an spreadsheet, applied my formula and the result was 75.
I then copied the data from my post, put it a spreadsheet, applied my formula and the result was 75.
Obviously you are doing something differently than I am because my formula returns 75 for the sample data given.
0
DerbyDad03 went over this in Response 5 but
Did you Copy & Paste the amounts?If you did, then the amounts are entered as TEXT not Numbers.
Try entering the amounts from the keyboard and see if that helps.
0
The first thing I would do is verify the problem.
If you have £150 in A1, I would use =ISNUMBER(A1) in some other cell. If the result was FALSE, then the contents of A1 is text, regardless of how it was formatted.
One possible way to convert the contents from text to numbers would be to select all the values and use Edit Replace. Put a £ in the Find what: field, leave the Replace with: field blank and click Replace All.
Hopefully your ISNUMBER test is now TRUE.
If not, select any empty cell, press Ctrl-C for Copy, select your list of values that no longer have the £ and do an Edit...PasteSpecial...Add.
Now format the cells as currency with the £ if desired.
0
I've PM'ed an email address for you to send the workbook to.
For some reason, which we obviously can't see from here, Excel is not considering your £-values to be numbers.
Send me a copy of the workbook and I'll see what I can do.
0
Check your private messages. I've sent it to you there - twice.
Please do not share it with anyone.
0
I see no sense in repeating the same process a third time.
Private Messages do not disappear unless you manually select them for removal.
Check your Private Messages under My Home.
I'm not going to post the email address in a public forum.
0  |
 i cant send and recive ma...
|
not loading
|
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
