# sumif function or sum function

April 24, 2009 at 15:27:06
Specs: Windows Vista
 i am using open office version 3.0i 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)

See More: sumif function or sum function

#1
April 24, 2009 at 18:35:34
 Something similar to this might work:=SUMIF(A1:A4,"=*mobile*",B1:B4)

Report •

#2
April 25, 2009 at 23:24:18
 thanx for help but its also not working. i got result 0 can u help me more please????

Report •

#3
April 26, 2009 at 04:06:56
 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.

Report •

Related Solutions

#4
April 26, 2009 at 18:36:35
 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.004/2/09 Lycatel bill (mobile) £10.005/2/09 Rent £150.0010/2/09 Top up (mobile) £15.00i need sum of amount which contain ward 'mobile' in description. means here i want result is £75.

Report •

#5
April 27, 2009 at 06:27:00
 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 ```

Report •

#6
April 27, 2009 at 12:25:04
 hi sir/medum,Amount is already set as currency. but its not working. it give result 0.

Report •

#7
April 27, 2009 at 16:30:35
 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.

Report •

#8
April 27, 2009 at 17:33:16
 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.MIKEhttp://www.skeptic.com/

Report •

#9
April 28, 2009 at 00:10:10
 so what i have to do?

Report •

#10
April 28, 2009 at 04:21:50
 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.

Report •

#11
April 28, 2009 at 06:17:14
 i had done this but isnumber giving me FALSE .

Report •

#12
May 4, 2009 at 14:08:35
 hi sir can you help me please to solve dat problem??

Report •

#13
May 4, 2009 at 17:08:40
 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.

Report •

#14
May 4, 2009 at 20:41:20
 dear sir/madam,Can you give me your e-mail id so i can send you work book.

Report •

#15
May 4, 2009 at 21:48:44
 Check your private messages. I've sent it to you there - twice.Please do not share it with anyone.

Report •

#16
May 5, 2009 at 11:55:55