Conditional sum wizard

[ronb]

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

 

[T. Valko]

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.

 

[T. Valko]

=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)

 

[T. Valko]

mark

--
Biff
Microsoft Excel MVP

Ooops!

I left out an opening parenthesis:

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