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

Help is much appreciated


--
SamGB
------------------------------------------------------------------------
SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

You can reference cells in SUMPRODUCT. For instance:

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

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=516363

HAHA as simple as that, cheers matey


--
SamGB
------------------------------------------------------------------------
SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

Try

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"SamGB" wrote
in message
...

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

Help is much appreciated


--
SamGB
------------------------------------------------------------------------
SamGB's Profile:
http://www.excelforum.com/member.php...o&userid=31263
View this thread:
http://www.excelforum.com/showthread...hreadid=516363

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

Regards

Trevor


"SamGB" wrote in message
...

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

Help is much appreciated


--
SamGB
------------------------------------------------------------------------
SamGB's Profile:
http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

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

"SamGB" wrote:


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

Help is much appreciated


--
SamGB
------------------------------------------------------------------------
SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

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!

Cheers


--
SamGB
------------------------------------------------------------------------
SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

SUMPRODUCT does not work on columns.

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

Regards

Trevor


"SamGB" wrote in
message ...

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!

Cheers


--
SamGB
------------------------------------------------------------------------
SamGB's Profile:
http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

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??


--
SamGB
------------------------------------------------------------------------
SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

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?

"Trevor Shuttleworth" wrote:

SUMPRODUCT does not work on columns.

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

Regards

Trevor


"SamGB" wrote in
message ...

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!

Cheers


--
SamGB
------------------------------------------------------------------------
SamGB's Profile:
http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363

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


"Sloth" wrote in message
...
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?

"Trevor Shuttleworth" wrote:

SUMPRODUCT does not work on columns.

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

Regards

Trevor


"SamGB" wrote in
message ...

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!

Cheers


--
SamGB
------------------------------------------------------------------------
SamGB's Profile:
http://www.excelforum.com/member.php...o&userid=31263
View this thread:
http://www.excelforum.com/showthread...hreadid=516363

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


"SamGB" wrote in
message ...

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??


--
SamGB
------------------------------------------------------------------------
SamGB's Profile:
http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516363