#NUM!

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

{=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2))}

I am getting a #NUM!

The formula is in A2 in Sheet2.

The object is to Find the value in Col A:A Sheet1 and
copy it to Sheet2.

Thankyou.
 
SUMPRODUCT cannot handle entire column references. Change
your references to something like:

Sheet1!C1:C2000

HTH
Jason
Atlanta, GA
 
Hello from Steved

{=SUMPRODUCT((Sheet1!C1:C2000=B2),(Sheet1!D1:D2000=$C$2))}

Returns 0

Any ideas please.

Thankyou.
 
how about:

=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1!D1:D2000=$C$2))
or
=SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1!D1:D2000=$C$2))

(no need to array enter this)


The -- stuff changes true/falses to 1/0's. And =sumproduct() likes to work with
numbers.
 
Hello from Steved

=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1!
D1:D2000=$C$2))

The above is giving me a 1 in this case it should be 4509.

My understanding is sumproduct will copy a value and
paste it. Am I using The right formula please.

The above formula is in B2 of sheet2 it is to copy the
value from Col A:A of Sheet1.

Thankyou.
-----Original Message-----
how about:

=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2))
or
=SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1!D1:D2000=$C$2))

(no need to array enter this)


The -- stuff changes true/falses to 1/0's. And
Click to expand...
=sumproduct() likes to work with
 
Hello from Steved

=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1!
D1:D2000=$C$2))

The above is giving me a 1 in this case it should be 4509.

My understanding is sumproduct will copy a value and
paste it. Am I using The right formula please.

The above formula is in B2 of sheet2 it is to copy the
value from Col A:A of Sheet1.

Thankyou.
-----Original Message-----
how about:

=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2))
or
=SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1!D1:D2000=$C$2))

(no need to array enter this)


The -- stuff changes true/falses to 1/0's. And
Click to expand...
=sumproduct() likes to work with
 
The most that this can be is: 2000.

=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1!D1:D2000=$C$2))

says to count the cells in C1:C2000 that match B2 and at the same time the cells
in D1:D2000 that match C2.

If all the cells match (correspondingly), then it can never exceed 2000. (Until
you include more cells.)

And formulas don't copy values and paste them. They do evaluate and put show
you what the calculated value will be, though.
 
Thankyou.
-----Original Message-----
The most that this can be is: 2000.

=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2))

says to count the cells in C1:C2000 that match B2 and at the same time the cells
in D1:D2000 that match C2.

If all the cells match (correspondingly), then it can never exceed 2000. (Until
you include more cells.)

And formulas don't copy values and paste them. They do evaluate and put show
you what the calculated value will be, though.
Click to expand...
 
Back
Top