SUMPRODUCT Help please

S

SamGB

Im using this formula: =SUMPRODUCT(--(C6:C403="??"),--(D6:D403="??")
which works well, however where the "??" are placed i would like t
enter a cell location i.e "A6" which will use whatever text is in th
cell to perform the sum. Is this possible? it would save alot of tim
rather than changing criteria in the formula everytime

Help is much appreciate
 
S

SteveG

You can reference cells in SUMPRODUCT. For instance:

=SUMPRODUCT(--(C6:C403=A6),--(D6:D403=A6))

Steve
 
S

SamGB

Cheers guys,
could you help me with this problem aswell,
im using this formula:
SUMPRODUCT(--(Sheet Name!$C:$C="value"),--('Sheet Name!$D:$D="value"))

but its coming out as product: #NUM!

Cheer
 
T

Trevor Shuttleworth

SUMPRODUCT does not work on columns.

SUMPRODUCT(--(Sheet Name!$C1:$C65536="value"),--('Sheet
Name!$D1:$D65536="value"))

Regards

Trevor
 
S

SamGB

Isnt $C1:$C65536 and $D1:$D65536 a column anyway?

What i want to do is copy the formula into alot of decending cells, but
every time i copy down a cell the value increases by one which is what i
WANT. However the range also increase by one which i DONT want. Any
ideas??
 
S

Sloth

I tried a SUMPRODUCT using C1:C65536 and it converted to C:C and gave me an
error. I then tried C1:C65535 and it worked. Why can you do the whole row,
but I can't?
 
T

Trevor Shuttleworth

That's because I can't either :-(

I didn't check that C1 to C65536 converted to C:C. What you did is right
.... you can't use SUMPRODUCT on a full column.

Regards
 
T

Trevor Shuttleworth

Yes, it is ... use $C1:$C65535 as Sloth suggested

Make the range absolute so that it doesn't increment

$C$1:$C$65535 and $D$1:$CD65535

Regards

Trevor
 
Top