Calculations in Excel

marka87uk

OK, for a start I'm not using Excel, but OOo, but I'm thinking most formulas are the same...

I have two columns, one has an item, and one has a price.

Now I want to calculate the sum of the prices for a certain item (PETROL), so I need to basically say in pseudo code I guess, "if the cell to the right contains 'PETROL' then add this cell's value to the sum".

It's been a while since I did formulas... any help appreciated, thanks.

slowen

It's been a while since I have also done formulas but I will give it a go

For example

Cell A1 = Item
Cell B1 = Price
Cell C1 = Answer

In cell C1 do the following forumula =sum(A1+B1) then this will add the two numbers together and give you the answer in cell 3

If you want to multiply or divide just change the + to X or /

Hopefully this should work ok

Chippy_Minton

Use the SUMIF worksheet function. E.g.:

=SUMIF(B2:B5,"=PETROL",A2: A5)

Prices are in column A, items are in column B. Column headings are in row 1.

marka87uk

Slowen, that's not what I wanted, but thanks anyway. The SUMIF function seems to be what I want but I can't get it to work... just comes up with #NAME?.

I'm using this -
=SUMIF(B3:B53,"=PETROL",C3:C53)

I've tried with and without the equals sign in front of PETROL.

Column A = Date (nothing to do with the formula)
Column B = Item (where it should see if the word is PETROL)
Column C = Price

Any ideas?

Stevenj214

=sumif(b3:b53,"petrol",c3:c53)

works in excel

marka87uk

Right, I guess it's just a problem with OOo then, but it supports the SUMIF formula... Hum oh well.

EDIT - Got it, OOo requires a semi-colon rather than a comma.

=SUMIF(b3:b53;"PETROL";c3:c53)

homersimpson_3

are you simply trying to add up the numbers/prices etc in a column? if so why don't you just use autosum? - computer puts in formulae for you - the trick to using excel is think logically and keep it simple.

marka87uk

Don't worry it's been solved now homersimpson... using SUMIF, but OOo required semi-colons as opposed to comma's to seperate values.

IvanOpinion

Another thing you could try is to use a pivot table. It can do all the summation automatically for you and can be refreshed if you add more data into the original sheet. They are a bit tricky to get into but if you do a search on the Microsft site you will find a couple of good tutorials related to pivot tables.

Ivan