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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"jhicsupt" wrote in message
...
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.