Calculate the sum of items in one column if condition other column = true

Hi everybody,

I've got a problem I can't find the solution for, if there is one. I want to calculate the sum of all the items in column1 that match a certain condition in column2.

You can see an example of what I mean in the attached file. As you can see I've solved the problem by adding an additional column for each category, but I don't think this is a very elegant way of doing this. It also poses some problems, e.g. the formulas don't always automatically get copied when I insert a new row etc.

So, my question is, can I write a formula like this in one single cell (not really an accurate piece of code, but I hope you understand what I mean):
Sum(
for i=1 to i=infinity
if(Ai=cat1;Bi;"")
)

I guess that's impossible without using macros? Has anybody got an idea? And if it's only possible with macros, how would I write one. I've got basic general programming skills (although it's been quite some time now), but I haven't written a macro before.

Thanks in advance for any help,

Eddy.

Hi again,

I seem to have encountered a new problem. In the actual document I need this for, I've got multiple conditions. I've googled and found the function SUMPRODUCT.

The previous condition SUMIF(A:A, 1, B:B) should only be calculated when there is no value in column M. So I come to this formula:
=SUMPRODUCT(--(M:M<>"");--(A:A=1);--(B:B))

But this results in #NUM!. However, I can't exactly see what I did wrong.

Also, is it possible to use other functions like ISBLANK in addition to SUMPRODUCT or IFSUM?

Thanks in advance,

Eddy