SUMPRODUCT Problem

[spalton]

Hi guys, seem to have lost my marbles, haven't done any excel work for a while and seem to have forgotten a formula, hope you can help remind me.

In Column A I have a list of names, column B I have a postive or negative income figure. I've put the name I'm looking in A102.

I need to sum the total of the positives in column C and in column D sum up the total of the negatives.

I'm using =SUMPRODUCT((A1:A100=A102)*(B1:B100>0)) but this just counts up the number of positives, it dosn't give me the Total of all of the values added together. Obviously once I've got this it will be a simple task to total the negatives.

Can you help me? Thankyou!

 

[VoG]

Try

=SUMPRODUCT(--(A1:A100=A102),--(B1:B100>0),B1:B100)

 

[spalton]

Awesome! Thankyou, it worked perfectly!