excel help SUM for columnB IF ColumnA = labelx

[Golf1.6]

Doing my self assessment

Also got a double whammy because I need to produce a p&l to show the council.

They would need to see a breakdown of expenses and won't be satasifid like HMRC who are happy to see the total income/expense as lump sums.

This should be straightforward and easy to google IF I knew what this function was called.

Basically I want to keep all my expenses in date order in the excel spreadsheet.

But I need to group all totals of a certain expense type liek mobile phone, web hosting etc.

So I will have the expense type as a column in the spreadsheet.

So question is:

How would I a total of all cells in a column where adjacent cell next to the amount is "Web hosting" for instance?

Also after I've categoried all the expenses, chances are there will be some misc payments that are not categoried. How do I define NULL in excel? I would likee to create an entry for Other expenses and catch all the entries that do not have a category.

Thanks for reading.

 

[james.miller]

=SUMIF(A:A,"Web Hosting",B:B)

That will sum everything in column B where column A = "Web Hosting".

=SUMIF(A:A,"",B:B)

That will sum if if there's nothing in column A.

 

[Golf1.6]

Thanks a lot. It worked brilliantly.

Just out of curiosity, I'd like to use seperate labels for internet, telephone costs. But for the sake of simplicty it's better to group small entries like Internet & telephone as one entry in the p&l

I would I use the formula to add up the total for multiple labels. How would I go about adding the total of column B where Column A is either Internet OR telephone

Thanks

 

[Namsnik]

=SUMIF(A:A,"Internet",B:B)+SUMIF(A:A,"Telephone",B:B)

However a pivot table would give you a great little summary and would help make sure that you didn't miss a Type of Expense. If you want help with that post back but tell us which version of Office you are using.

You can't add together two Types of Expense in a pivot so would either have to create a summary from the pivot or better add a column into your table for the Category. I would use a lookup table and a vlookup formula for that but one step at a time .

If your data looks like this:

Then a pivot could do this:

Or you could even have a "Dashboard" looking like this: