SUMPRODUCT Help please

[SamGB]

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

PLEASE LOOK TO BOTTOM OF POST

[SteveG]

You can reference cells in SUMPRODUCT. For instance:

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

Steve

[SamGB]

HAHA as simple as that, cheers matey

[Chip Pearson]

Try

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


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

"SamGB" <[email protected]> wrote
in message
news:[email protected]...
>
> 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
>

[Trevor Shuttleworth]

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

Regards

Trevor


"SamGB" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Sloth]

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

[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!

Cheers

[Trevor Shuttleworth]

SUMPRODUCT does not work on columns.

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

Regards

Trevor


"SamGB" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Trevor Shuttleworth]

SUMPRODUCT does not work on columns.

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

Regards

Trevor


"SamGB" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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

"Trevor Shuttleworth" wrote:

> SUMPRODUCT does not work on columns.
>
> SUMPRODUCT(--(Sheet Name!$C1:$C65536="value"),--('Sheet
> Name!$D1:$D65536="value"))
>
> Regards
>
> Trevor
>
>
> "SamGB" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

[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


"Sloth" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>>

[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


"SamGB" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>