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,

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,

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.

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.

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,