I am trying to sum values in a column on a worksheet, using criteria in two
other columns on the same worksheet, which are defined by one absolute and
one relative reference on a second worksheet. The wizard works when I use two
simple criteria, such as "sam" and "1.5", but does not work when I try to use
criteria located in cells on the second worksheet, such as "$E$2" and "f4" --
ronb

You didn't provide enough specific detail.

Try something like this:

=SUMPRODUCT(--A1:A100=Sheet2!$E$2),--(B1:B100=Sheet2!F4),C1:C100)

Where C1:C100 is the range to sum.

You obviously want to copy the formula so use the appropriate reference
style on the ranges A1:A100, B1:B100 and C1:C100.

--
Biff
Microsoft Excel MVP


"ronb" wrote:

I am trying to sum values in a column on a worksheet, using criteria in two
other columns on the same worksheet, which are defined by one absolute and
one relative reference on a second worksheet. The wizard works when I use two
simple criteria, such as "sam" and "1.5", but does not work when I try to use
criteria located in cells on the second worksheet, such as "$E$2" and "f4" --
ronb

=SUMPRODUCT(--A1:A100=Sheet2!$E$2),--(B1:B100=Sheet2!F4),C1:C100)

Ooops!

I left out an opening parenthesis:

=SUMPRODUCT(--(A1:A100=Sheet2!$E$2),--(B1:B100=Sheet2!F4),C1:C100)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote:

You didn't provide enough specific detail.

Try something like this:

=SUMPRODUCT(--A1:A100=Sheet2!$E$2),--(B1:B100=Sheet2!F4),C1:C100)

Where C1:C100 is the range to sum.

You obviously want to copy the formula so use the appropriate reference
style on the ranges A1:A100, B1:B100 and C1:C100.

--
Biff
Microsoft Excel MVP


"ronb" wrote:

I am trying to sum values in a column on a worksheet, using criteria in two
other columns on the same worksheet, which are defined by one absolute and
one relative reference on a second worksheet. The wizard works when I use two
simple criteria, such as "sam" and "1.5", but does not work when I try to use
criteria located in cells on the second worksheet, such as "$E$2" and "f4" --
ronb

mark

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=SUMPRODUCT(--A1:A100=Sheet2!$E$2),--(B1:B100=Sheet2!F4),C1:C100)

Ooops!

I left out an opening parenthesis:

=SUMPRODUCT(--(A1:A100=Sheet2!$E$2),--(B1:B100=Sheet2!F4),C1:C100)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote:

You didn't provide enough specific detail.

Try something like this:

=SUMPRODUCT(--A1:A100=Sheet2!$E$2),--(B1:B100=Sheet2!F4),C1:C100)

Where C1:C100 is the range to sum.

You obviously want to copy the formula so use the appropriate reference
style on the ranges A1:A100, B1:B100 and C1:C100.

--
Biff
Microsoft Excel MVP


"ronb" wrote:

I am trying to sum values in a column on a worksheet, using criteria in
two
other columns on the same worksheet, which are defined by one absolute
and
one relative reference on a second worksheet. The wizard works when I
use two
simple criteria, such as "sam" and "1.5", but does not work when I try
to use
criteria located in cells on the second worksheet, such as "$E$2" and
"f4" --
ronb