HELP ON SUMPRODUCT() FUNCTION

[Eddy Stan]

Hi,

=SUMPRODUCT(--(DecMaxKms>($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel>(D$23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23))

I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial
ac8:ac19478, and so on..). At the same time I have a range name DecAll with
range ab8:af19478, to get sum of all columns with given condition, there I
get error as "Value#".

Can You all help me out ? many thanks in advance.

When I just put = sumproduct(decall) - it is giving result, but with
condition like above it is throwing error. Am I expecting more or I have done
some error, please help me.

[Bob Phillips]

For the last one you need

=SUMPRODUCT((DecMaxKms>($B26-1))*(DecMaxKms<=($C26))*
(DecTruckModel>(D$23-1))*(DecTruckModel<=(D$24))*
(INDIRECT("DEC"&$B$23)))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
>
=SUMPRODUCT(--(DecMaxKms>($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel>(D
$23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23))
>
> I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial
> ac8:ac19478, and so on..). At the same time I have a range name DecAll
with
> range ab8:af19478, to get sum of all columns with given condition, there I
> get error as "Value#".
>
> Can You all help me out ? many thanks in advance.
>
> When I just put = sumproduct(decall) - it is giving result, but with
> condition like above it is throwing error. Am I expecting more or I have
done
> some error, please help me.
>

[Tom Ogilvy]

for multicolumn DecAll do it this way
=SUMPRODUCT((DecMaxKms>($B26-1))*(DecMaxKms<=$C26)*DecAll)

--
Regards,
Tom Ogilvy


"Eddy Stan" wrote:

> Hi,
>
> =SUMPRODUCT(--(DecMaxKms>($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel>(D$23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23))
>
> I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial
> ac8:ac19478, and so on..). At the same time I have a range name DecAll with
> range ab8:af19478, to get sum of all columns with given condition, there I
> get error as "Value#".
>
> Can You all help me out ? many thanks in advance.
>
> When I just put = sumproduct(decall) - it is giving result, but with
> condition like above it is throwing error. Am I expecting more or I have done
> some error, please help me.
>

[Eddy Stan]

Hi,
I copied your formula, still I get error. BUT I redefined the range name
like DMax & DAll, it worked - for my multiple range sum request. But the same
formula did not work to find sum for Hire, Special, Extras, Halting...
separately.
That is my b23 is a validation cell having list of Hire, Special, Extras &
All also.
Which mean my user should be able to for single as well as for all.
So Do I have to check for "All" & put this formula and if not "All" put the
previous formula ??
For the time being I am checking like if($b$23="All",this formula,previous
formula)
or do I have comman sumproduct() & I am missing something ? kindly Clarify.

Thank you for the contribution.

"Bob Phillips" wrote:

> For the last one you need
>
> =SUMPRODUCT((DecMaxKms>($B26-1))*(DecMaxKms<=($C26))*
> (DecTruckModel>(D$23-1))*(DecTruckModel<=(D$24))*
> (INDIRECT("DEC"&$B$23)))
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Eddy Stan" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> >
> =SUMPRODUCT(--(DecMaxKms>($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel>(D
> $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23))
> >
> > I have ranges to find sum (sum ranges like DecHire ab8:ab19478, DecSpecial
> > ac8:ac19478, and so on..). At the same time I have a range name DecAll
> with
> > range ab8:af19478, to get sum of all columns with given condition, there I
> > get error as "Value#".
> >
> > Can You all help me out ? many thanks in advance.
> >
> > When I just put = sumproduct(decall) - it is giving result, but with
> > condition like above it is throwing error. Am I expecting more or I have
> done
> > some error, please help me.
> >
>
>
>

[Bob Phillips]

No, it should work for any value. One thing to check is that all the named
ranges are the same size, SP depends upon that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I copied your formula, still I get error. BUT I redefined the range name
> like DMax & DAll, it worked - for my multiple range sum request. But the
same
> formula did not work to find sum for Hire, Special, Extras, Halting...
> separately.
> That is my b23 is a validation cell having list of Hire, Special, Extras &
> All also.
> Which mean my user should be able to for single as well as for all.
> So Do I have to check for "All" & put this formula and if not "All" put
the
> previous formula ??
> For the time being I am checking like if($b$23="All",this formula,previous
> formula)
> or do I have comman sumproduct() & I am missing something ? kindly
Clarify.
>
> Thank you for the contribution.
>
> "Bob Phillips" wrote:
>
> > For the last one you need
> >
> > =SUMPRODUCT((DecMaxKms>($B26-1))*(DecMaxKms<=($C26))*
> > (DecTruckModel>(D$23-1))*(DecTruckModel<=(D$24))*
> > (INDIRECT("DEC"&$B$23)))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Eddy Stan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > >
> >
=SUMPRODUCT(--(DecMaxKms>($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel>(D
> > $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23))
> > >
> > > I have ranges to find sum (sum ranges like DecHire ab8:ab19478,
DecSpecial
> > > ac8:ac19478, and so on..). At the same time I have a range name DecAll
> > with
> > > range ab8:af19478, to get sum of all columns with given condition,
there I
> > > get error as "Value#".
> > >
> > > Can You all help me out ? many thanks in advance.
> > >
> > > When I just put = sumproduct(decall) - it is giving result, but with
> > > condition like above it is throwing error. Am I expecting more or I
have
> > done
> > > some error, please help me.
> > >
> >
> >
> >

[Eddy Stan]

Hi Bob,
Thanks for the same. I check the range sizes and they are all of same size.
It is a big file 13mb (19497 rows of columns up to AW, where I use SP based
on 2 columns giving upper & lower range to sum 7 columns individually & once
all together.
I checked the results by filter & subtotal() the result was 207K with SP it
was 1086k (I thought it was correct as it didn't throw error), since the
variation was huge I created 7 blocks to find individual results to find
total which came exactly 207k.
I was unable to find why it showed 1086k. Ordinary sum of 7 columns ae
3347k. So the SP is doing something but unable to understand the parameter or
my worksheet has some confusing thing, it was just SAP exported data.

Thanks again to you & Tom


"Bob Phillips" wrote:

> No, it should work for any value. One thing to check is that all the named
> ranges are the same size, SP depends upon that.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Eddy Stan" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > I copied your formula, still I get error. BUT I redefined the range name
> > like DMax & DAll, it worked - for my multiple range sum request. But the
> same
> > formula did not work to find sum for Hire, Special, Extras, Halting...
> > separately.
> > That is my b23 is a validation cell having list of Hire, Special, Extras &
> > All also.
> > Which mean my user should be able to for single as well as for all.
> > So Do I have to check for "All" & put this formula and if not "All" put
> the
> > previous formula ??
> > For the time being I am checking like if($b$23="All",this formula,previous
> > formula)
> > or do I have comman sumproduct() & I am missing something ? kindly
> Clarify.
> >
> > Thank you for the contribution.
> >
> > "Bob Phillips" wrote:
> >
> > > For the last one you need
> > >
> > > =SUMPRODUCT((DecMaxKms>($B26-1))*(DecMaxKms<=($C26))*
> > > (DecTruckModel>(D$23-1))*(DecTruckModel<=(D$24))*
> > > (INDIRECT("DEC"&$B$23)))
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Eddy Stan" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi,
> > > >
> > > >
> > >
> =SUMPRODUCT(--(DecMaxKms>($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel>(D
> > > $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23))
> > > >
> > > > I have ranges to find sum (sum ranges like DecHire ab8:ab19478,
> DecSpecial
> > > > ac8:ac19478, and so on..). At the same time I have a range name DecAll
> > > with
> > > > range ab8:af19478, to get sum of all columns with given condition,
> there I
> > > > get error as "Value#".
> > > >
> > > > Can You all help me out ? many thanks in advance.
> > > >
> > > > When I just put = sumproduct(decall) - it is giving result, but with
> > > > condition like above it is throwing error. Am I expecting more or I
> have
> > > done
> > > > some error, please help me.
> > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Eddy,

It is very difficult to see the problem without seeing the workbook. All I
know is that the technique works.

Perhaps you could take a copy of the workbook and remove some rows of data,
and keep doing that until the results are the same as filter subtotal. Once
you get there, restore the last deleted data and home in that data removing
bit by bit, until you home in on the cause.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
> Thanks for the same. I check the range sizes and they are all of same
size.
> It is a big file 13mb (19497 rows of columns up to AW, where I use SP
based
> on 2 columns giving upper & lower range to sum 7 columns individually &
once
> all together.
> I checked the results by filter & subtotal() the result was 207K with SP
it
> was 1086k (I thought it was correct as it didn't throw error), since the
> variation was huge I created 7 blocks to find individual results to find
> total which came exactly 207k.
> I was unable to find why it showed 1086k. Ordinary sum of 7 columns ae
> 3347k. So the SP is doing something but unable to understand the parameter
or
> my worksheet has some confusing thing, it was just SAP exported data.
>
> Thanks again to you & Tom
>
>
> "Bob Phillips" wrote:
>
> > No, it should work for any value. One thing to check is that all the
named
> > ranges are the same size, SP depends upon that.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Eddy Stan" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > > I copied your formula, still I get error. BUT I redefined the range
name
> > > like DMax & DAll, it worked - for my multiple range sum request. But
the
> > same
> > > formula did not work to find sum for Hire, Special, Extras, Halting...
> > > separately.
> > > That is my b23 is a validation cell having list of Hire, Special,
Extras &
> > > All also.
> > > Which mean my user should be able to for single as well as for all.
> > > So Do I have to check for "All" & put this formula and if not "All"
put
> > the
> > > previous formula ??
> > > For the time being I am checking like if($b$23="All",this
formula,previous
> > > formula)
> > > or do I have comman sumproduct() & I am missing something ? kindly
> > Clarify.
> > >
> > > Thank you for the contribution.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > For the last one you need
> > > >
> > > > =SUMPRODUCT((DecMaxKms>($B26-1))*(DecMaxKms<=($C26))*
> > > > (DecTruckModel>(D$23-1))*(DecTruckModel<=(D$24))*
> > > > (INDIRECT("DEC"&$B$23)))
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Eddy Stan" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi,
> > > > >
> > > > >
> > > >
> >
=SUMPRODUCT(--(DecMaxKms>($B26-1)),--(DecMaxKms<=($C26)),--(DecTruckModel>(D
> > > > $23-1)),--(DecTruckModel<=(D$24)),INDIRECT("DEC"&$B$23))
> > > > >
> > > > > I have ranges to find sum (sum ranges like DecHire ab8:ab19478,
> > DecSpecial
> > > > > ac8:ac19478, and so on..). At the same time I have a range name
DecAll
> > > > with
> > > > > range ab8:af19478, to get sum of all columns with given condition,
> > there I
> > > > > get error as "Value#".
> > > > >
> > > > > Can You all help me out ? many thanks in advance.
> > > > >
> > > > > When I just put = sumproduct(decall) - it is giving result, but
with
> > > > > condition like above it is throwing error. Am I expecting more or
I
> > have
> > > > done
> > > > > some error, please help me.
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >