I don't know how to do the following: I created a drop down list using validation function. the list is about categories of expenses (ie food, petrol etc). I have another column with amount in the same row.

Now at the end of the sheet I want to have a 'total' cell, where it sums up all the amount from a given category (so all food expenses are sum up in one cell).I would really appreciate your help!

Is that what you need? With a drop down in A1, a list of categories in B1:B10 and a list of corresponding amounts in C1:C10, try this formula:

=SUMIF(C1:C10,A1,D1:D10)

This will find each occurrence of the the value in A1 in B1:B10 and sum the corresponding values from C1:C10.

Hi there, Didn't really work, I think my description wasn't clear enough. So lets try again:

Its a sheet with following columns:

A B C D E

Date Description Amount Category Balance.In each single row I want to be able to put a single entry of expense, ie buying petrol on the station would look something like this:

03/09 Petrol -9£ Petrol 90£

Now in every cell in the row D there is a drop down list with some categories (food/petrol/etc)

Now in Column G I would like to have a list of all the categories:

1 Petrol

2 Food

3 Leisure

4 Travel

5 Bills

...and then in an empty cell next to each category in the column F, a formula that will find every occurrence of lets say petrol in column D, pair it with the amount in the following cell from column C until 'the end' of sheet add it up and come up with the amount of how much I spent on petrol.

hopes that helps and someone would be able to solve my problem.

This is what worked for me... =SUMIF(D2:D20;"Petrol";C2:C20)

I will break it down in to parts:

=SUMIF function command

(D2:D20) is the cell range that you select for your catergory (petrol, food, etc)

"Petrol" is the catergory you are adding up (if you are adding food, type in food instead of petrol)

C2:C20 is the range of you price/amountHope this helps!

Ask Your Question

Weekly Poll