Sumproduct
 

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.

Sumproduct
 

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.

Sumproduct
 

Can you provide some more detail about what you're looking to do?
Specifically, when you say "IF A2:a100 in Worksheet 2 = column A in worksheet
1", it's not clear WHAT you want to test: sum, count, product, average?

Same with your second test "column B in Worksheet2 < 1": 1 what? One
count? The value of 1? An average of 1?

Dave
--
Brevity is the soul of wit.


"jhicsupt" wrote:

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.

Sumproduct
 

Thanks for quick response.

Heres what Im trying to do.

I have two worksheets.

Sheet1
A2 A3
John Doe this is where I want the result of the formula

Sheet2
Column A Column B Column C
John Doe 1 5
John Smith 5

All of the employees are listed in Sheet1. So I want to check Sheet2 for
multiple criteria.

First I need to find John Doe in Sheet2. If John Doe is on Sheet2 A50, then
check Sheet2 A51.
If Sheet2 A51=1, then no need to go any further, enter a 0 in Sheet1 A3.
If Sheet2 A51<1, then go to Sheet2 A52 and see if theres a 5.
If theres a 5, in Sheet1 A3, put a 5.
If theres not a 5, in Sheet1 A3, put a 0.

Hopefully this is still not too confusing.

Here what I have, but it's returning VALUE
=SUMPRODUCT(--(DailyAttendance!A4:A631=ASA!A4),--(DailyAttendance!M4:M631<1),--([ASALog.xls]ASAInput!$C$4=5),5,"")




=SUMPRODUCT(--(DailyAttendance!A4:A631=ASA!A4),--(DailyAttendance!M4:M631<1),--([ASALog.xls]ASAInput!$C$4=5),5,"")

"Bernard Liengme" wrote:

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.