Sumproduct

[Guest]

I have two conditions I need to sum on 2 different worksheets:

Worksheet1
Column A2
John Doe

Worksheet2
Column A2 Column B2 Column C2
John Doe 1 2
John Smith 2

I need to:
If A2:A100 in Worksheet2 = columnA in Worksheet1
and
column B in Worksheet2 <>1
Sum up Column C in worksheet1 in B2, otherwise 0

So in this instance B2: John Doe would be 0, but John Smith would be 2.

I hope I haven't made it too confusing. Please help.

Thanks.

 

[Bernard Liengme]

If the data in each sheet begins on row 2.
=SUMPRODUCT(--(Sheet2!$A$2:$A$30=Sheet1!A2),--(Sheet2!$B$2:$B$30<>1),Sheet2!$C$2:$C$30)
Of course , the 30 can be changed to whatever; but you cannot us a whole
column reference such as A:A (except, I think, with XL 2007)
best wishes