Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with two criteria on on difference worksheets
In Excel 2003 I have four columns on worksheet 1 and four columns on
worksheet 2. I want to sum column C on worksheet 1 if column A on worksheets 1 and 2 are the same three numbers (formatted as text); and the column D on worksheet 1 equals two letters (AB, CD, EF, etc). Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with two criteria on on difference worksheets
Hi
try the following =SUMPRODUCT((Sheet1!$A$1:$A$1000=Sheet2!$A$1:$A$10 00)*(Sheet1!$D$1:$D$1000="AB")*(Sheet1!$C$1:$C$100 0)) Change the ranges to suit, but note that XL2003 will not take whole columns as arguments. You could put your "AB" etc in a cell, and use the cell reference in the second term, rather than having to keep changing the formula -- Regards Roger Govier "y2d3" wrote in message ... In Excel 2003 I have four columns on worksheet 1 and four columns on worksheet 2. I want to sum column C on worksheet 1 if column A on worksheets 1 and 2 are the same three numbers (formatted as text); and the column D on worksheet 1 equals two letters (AB, CD, EF, etc). Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with two criteria on on difference worksheets
"Roger Govier" wrote: Hi try the following =SUMPRODUCT((Sheet1!$A$1:$A$1000=Sheet2!$A$1:$A$10 00)*(Sheet1!$D$1:$D$1000="AB")*(Sheet1!$C$1:$C$100 0)) Change the ranges to suit, but note that XL2003 will not take whole columns as arguments. You could put your "AB" etc in a cell, and use the cell reference in the second term, rather than having to keep changing the formula -- Regards Roger Govier "y2d3" wrote in message ... In Excel 2003 I have four columns on worksheet 1 and four columns on worksheet 2. I want to sum column C on worksheet 1 if column A on worksheets 1 and 2 are the same three numbers (formatted as text); and the column D on worksheet 1 equals two letters (AB, CD, EF, etc). Thanks, Hi Roger, This almost works. I get different results depending on the sort of column A (both worksheets) or column D worksheet 2. A sample of what I am doing is: Worksheet 1: Column A has 120 rows A B C 1 001 Formula 2 002 3 003 4 004 5 005 Worksheet 2: Contains 8,500 rows A B C D 1 002 001 15.42 AB 2 002 002 18.27 AB 300 002 003 12.22 CD 301 002 004 16.67 CD 1000 003 001 11.39 EF 1001 003 001 19.13 EF What I want to do is sum all of column C worksheet 2 where column A matches column A in worksheet 1 and column D in worksheet 2 equals AB. Thank you for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with two criteria on on difference worksheets
Hi
Modify the formula to =SUMPRODUCT((Sheet2!$A$1:$A$8500=Sheet1!A1)*(Sheet 1!$D$1:$D$8500="AB")*(Sheet1!$C$1:$C$8500)) Enter in C1 of Sheet 1 and copy down through C2:C120 -- Regards Roger Govier "y2d3" wrote in message ... "Roger Govier" wrote: Hi try the following =SUMPRODUCT((Sheet1!$A$1:$A$1000=Sheet2!$A$1:$A$10 00)*(Sheet1!$D$1:$D$1000="AB")*(Sheet1!$C$1:$C$100 0)) Change the ranges to suit, but note that XL2003 will not take whole columns as arguments. You could put your "AB" etc in a cell, and use the cell reference in the second term, rather than having to keep changing the formula -- Regards Roger Govier "y2d3" wrote in message ... In Excel 2003 I have four columns on worksheet 1 and four columns on worksheet 2. I want to sum column C on worksheet 1 if column A on worksheets 1 and 2 are the same three numbers (formatted as text); and the column D on worksheet 1 equals two letters (AB, CD, EF, etc). Thanks, Hi Roger, This almost works. I get different results depending on the sort of column A (both worksheets) or column D worksheet 2. A sample of what I am doing is: Worksheet 1: Column A has 120 rows A B C 1 001 Formula 2 002 3 003 4 004 5 005 Worksheet 2: Contains 8,500 rows A B C D 1 002 001 15.42 AB 2 002 002 18.27 AB 300 002 003 12.22 CD 301 002 004 16.67 CD 1000 003 001 11.39 EF 1001 003 001 19.13 EF What I want to do is sum all of column C worksheet 2 where column A matches column A in worksheet 1 and column D in worksheet 2 equals AB. Thank you for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with two criteria on on difference worksheets
"Roger Govier" wrote: Hi Modify the formula to =SUMPRODUCT((Sheet2!$A$1:$A$8500=Sheet1!A1)*(Sheet 1!$D$1:$D$8500="AB")*(Sheet1!$C$1:$C$8500)) Enter in C1 of Sheet 1 and copy down through C2:C120 -- Regards Roger Govier "y2d3" wrote in message ... "Roger Govier" wrote: Hi try the following =SUMPRODUCT((Sheet1!$A$1:$A$1000=Sheet2!$A$1:$A$10 00)*(Sheet1!$D$1:$D$1000="AB")*(Sheet1!$C$1:$C$100 0)) Change the ranges to suit, but note that XL2003 will not take whole columns as arguments. You could put your "AB" etc in a cell, and use the cell reference in the second term, rather than having to keep changing the formula -- Regards Roger Govier "y2d3" wrote in message ... In Excel 2003 I have four columns on worksheet 1 and four columns on worksheet 2. I want to sum column C on worksheet 1 if column A on worksheets 1 and 2 are the same three numbers (formatted as text); and the column D on worksheet 1 equals two letters (AB, CD, EF, etc). Thanks, Hi Roger, This almost works. I get different results depending on the sort of column A (both worksheets) or column D worksheet 2. A sample of what I am doing is: Worksheet 1: Column A has 120 rows A B C 1 001 Formula 2 002 3 003 4 004 5 005 Worksheet 2: Contains 8,500 rows A B C D 1 002 001 15.42 AB 2 002 002 18.27 AB 300 002 003 12.22 CD 301 002 004 16.67 CD 1000 003 001 11.39 EF 1001 003 001 19.13 EF What I want to do is sum all of column C worksheet 2 where column A matches column A in worksheet 1 and column D in worksheet 2 equals AB. Thank you for your help. Hi, Yes, this looks like it works. Thank you, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating the difference between two dates if two criteria are m | Excel Worksheet Functions | |||
Sum the difference between dates given a certain criteria | Excel Worksheet Functions | |||
sumif function with a difference | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
How do I compare 2 worksheets, 1 old, 1 updated to find difference | Excel Discussion (Misc queries) |