Issue with sumproduct

I believe you can only have the function operate on one column, so add
together 4 sumproducts (one for each of you columns).

"Steved" wrote:

> Hello from Steved
>
> Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> My objective is to sum the extra 3 columns.
> What is required please to have it working.
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
>
> Thankyou.

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Steved wrote:
>> Hello Aladin from Steved
>>
>> Thankyou I personally would not do it as I believe the less formulas the
>> better.
>>
>> Just speaking for myself.
> [...]
>
> Really?

I am perfectly sure that Aladin does not need me to talk for him and
probably his reply says much more than I am about to, but the number of
calculations in a formula is not always apparent at face value.

I stand to be corrected in this and if I am shot down in flames it will only
serve to increase my understanding of XL

If we take the formula that Regdyer gave (cut down to make it manageable)
=SUMPRODUCT(($A$4:$A$6=1)*($B$4:$B$6="P")*D4:G6) as an
example it, it looks like it has 3 evaluations in Column A + 3 evaluations
in
Column B+ 4 * 3 calculations in Columns G to G making a total of 18
calculations.

However, if we highlight (($A$4:$A$6=1) and press f9 we see
{True;True;True;True} as does ($B$4:$B$6="P"). D4:G6 produces
{ValueD4,ValueE4,ValueF4,ValueG4;
ValueD5,ValueE5,ValueF5,ValueG5;
ValueD6,ValueE6,ValueF6,ValueG6}

So when this is evaluated we get:

1*1*ValueD4 , 1*1*ValueE4 , 1*1*ValueF4 , 1*1*ValueG4 ;
1*1*ValueD5, ......but wait a minute where did those 1*1's come from? we
only had one set of TRUE's in each bracket and we have already used them.

Surely Columns A & B have to be evaluated again to provide the extra sets of
TRUE's.

If so then the true total of calculations is:

3 * 4 evaluations of Column A + 3 * 4 evaluations of Column B + 3 * 4
calculations in Columns D to G making a total of 36 calculations.

Aladin's suggestion uses 3 SUM calculations in Column H + 3 evaluations
in Column A + 3 evaluations in Column B + 3 calculations of the SUMs in
Column H making a total of 12 calculations - 1/3 of the original number!

So which solutuon has the fewer calculations?


--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Steved wrote:
> Hello Aladin from Steved
>
> Thankyou I personally would not do it as I believe the less formulas the
> better.
>
> Just speaking for myself.
[...]

Really?

Hello Aladin from Steved

Thankyou I personally would not do it as I believe the less formulas the
better.

Just speaking for myself.

Cheers.

"Aladin Akyurek" wrote:

> Create an additional column, say H, with from H4 on:
>
> =SUM(D4:G4)
>
> and invoke:
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!H4:H109)
>
> Steved wrote:
> > Hello from Steved
> >
> > Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> > My objective is to sum the extra 3 columns.
> > What is required please to have it working.
> >
> > =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> > Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
> >
> > Thankyou.
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.
>

Create an additional column, say H, with from H4 on:

=SUM(D4:G4)

and invoke:

=SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!H4:H109)

Steved wrote:
> Hello from Steved
>
> Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> My objective is to sum the extra 3 columns.
> What is required please to have it working.
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
>
> Thankyou.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Ragdyer Thankyou

"Ragdyer" wrote:

> This works for me:
>
> =SUMPRODUCT(($A$4:$A$109=1)*($B$4:$B$109="P")*D4:G109)
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > Hello from Steved
> >
> > Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> > My objective is to sum the extra 3 columns.
> > What is required please to have it working.
> >
> > =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> > Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
> >
> > Thankyou.
>
>

Thankyou Duane

"duane" wrote:

> I believe you can only have the function operate on one column, so add
> together 4 sumproducts (one for each of you columns).
>
> "Steved" wrote:
>
> > Hello from Steved
> >
> > Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> > My objective is to sum the extra 3 columns.
> > What is required please to have it working.
> >
> > =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> > Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
> >
> > Thankyou.

This works for me:

=SUMPRODUCT(($A$4:$A$109=1)*($B$4:$B$109="P")*D4:G109)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> My objective is to sum the extra 3 columns.
> What is required please to have it working.
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
>
> Thankyou.

Thankyou Duane

"duane" wrote:

> I believe you can only have the function operate on one column, so add
> together 4 sumproducts (one for each of you columns).
>
> "Steved" wrote:
>
> > Hello from Steved
> >
> > Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> > My objective is to sum the extra 3 columns.
> > What is required please to have it working.
> >
> > =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> > Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
> >
> > Thankyou.

Ragdyer Thankyou

"Ragdyer" wrote:

> This works for me:
>
> =SUMPRODUCT(($A$4:$A$109=1)*($B$4:$B$109="P")*D4:G109)
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > Hello from Steved
> >
> > Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> > My objective is to sum the extra 3 columns.
> > What is required please to have it working.
> >
> > =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> > Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
> >
> > Thankyou.
>
>

This works for me:

=SUMPRODUCT(($A$4:$A$109=1)*($B$4:$B$109="P")*D4:G109)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Steved" <[email protected]> wrote in message
news:[email protected]...
> Hello from Steved
>
> Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> My objective is to sum the extra 3 columns.
> What is required please to have it working.
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
>
> Thankyou.

Create an additional column, say H, with from H4 on:

=SUM(D4:G4)

and invoke:

=SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!H4:H109)

Steved wrote:
> Hello from Steved
>
> Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> My objective is to sum the extra 3 columns.
> What is required please to have it working.
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
>
> Thankyou.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Hello Aladin from Steved

Thankyou I personally would not do it as I believe the less formulas the
better.

Just speaking for myself.

Cheers.

"Aladin Akyurek" wrote:

> Create an additional column, say H, with from H4 on:
>
> =SUM(D4:G4)
>
> and invoke:
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!H4:H109)
>
> Steved wrote:
> > Hello from Steved
> >
> > Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> > My objective is to sum the extra 3 columns.
> > What is required please to have it working.
> >
> > =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> > Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
> >
> > Thankyou.
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.
>

Steved wrote:
> Hello Aladin from Steved
>
> Thankyou I personally would not do it as I believe the less formulas the
> better.
>
> Just speaking for myself.
[...]

Really?

I believe you can only have the function operate on one column, so add
together 4 sumproducts (one for each of you columns).

"Steved" wrote:

> Hello from Steved
>
> Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
> My objective is to sum the extra 3 columns.
> What is required please to have it working.
>
> =SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
> Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)
>
> Thankyou.

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Steved wrote:
>> Hello Aladin from Steved
>>
>> Thankyou I personally would not do it as I believe the less formulas the
>> better.
>>
>> Just speaking for myself.
> [...]
>
> Really?

I am perfectly sure that Aladin does not need me to talk for him and
probably his reply says much more than I am about to, but the number of
calculations in a formula is not always apparent at face value.

I stand to be corrected in this and if I am shot down in flames it will only
serve to increase my understanding of XL

If we take the formula that Regdyer gave (cut down to make it manageable)
=SUMPRODUCT(($A$4:$A$6=1)*($B$4:$B$6="P")*D4:G6) as an
example it, it looks like it has 3 evaluations in Column A + 3 evaluations
in
Column B+ 4 * 3 calculations in Columns G to G making a total of 18
calculations.

However, if we highlight (($A$4:$A$6=1) and press f9 we see
{True;True;True;True} as does ($B$4:$B$6="P"). D4:G6 produces
{ValueD4,ValueE4,ValueF4,ValueG4;
ValueD5,ValueE5,ValueF5,ValueG5;
ValueD6,ValueE6,ValueF6,ValueG6}

So when this is evaluated we get:

1*1*ValueD4 , 1*1*ValueE4 , 1*1*ValueF4 , 1*1*ValueG4 ;
1*1*ValueD5, ......but wait a minute where did those 1*1's come from? we
only had one set of TRUE's in each bracket and we have already used them.

Surely Columns A & B have to be evaluated again to provide the extra sets of
TRUE's.

If so then the true total of calculations is:

3 * 4 evaluations of Column A + 3 * 4 evaluations of Column B + 3 * 4
calculations in Columns D to G making a total of 36 calculations.

Aladin's suggestion uses 3 SUM calculations in Column H + 3 evaluations
in Column A + 3 evaluations in Column B + 3 calculations of the SUMs in
Column H making a total of 12 calculations - 1/3 of the original number!

So which solutuon has the fewer calculations?


--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Hello from Steved

Below is fine for D4:D109 but if I change it to D4:G109 it shows #VALUE!
My objective is to sum the extra 3 columns.
What is required please to have it working.

=SUMPRODUCT(--('Planned Kilometres'!$A$4:$A$109=1),--('Planned
Kilometres'!$B$4:$B$109="P"),'Planned Kilometres'!D4:D109)

Thankyou.