Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |