HELP ON SUMPRODUCT() FUNCTION

[Guest]

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)

 

[Guest]

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

 

[Guest]

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]

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)

 

[Guest]

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]

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)